Display PostgreSQL JSON in PHP

Question

I am querying 3 tables in PostgreSQL and converting the result into a nested json object. I am using PHP (and the Slim framework) as my application code. I am able to return a valid nested JSON object using PostgreSQL's built-in json operators. However, I'm having trouble rendering that JSON object in PHP.

PHP's json_encode operator seems to be trying to perform a JSON conversion on the provided obect (which is already in JSON format). And I can't get PHP to simply echo, print (or print_r) out the valid result provided by PostgreSQL. PHP keeps adding extra characters to the result.... I've tried :

(a) not using the PostgreSQL JSON operators,
(b) using PHP's json_decode before using json_encode,
(c) simply running echo and print_r statements, and
(d) I just started toying with using str_replace to remove the extraneous characters, but that is starting to feel scuzzy...

I'm pretty sure I'm doing something obviously wrong. I'm just at a standstill on this one. Any help is greatly appreciated!

The 3 tables are : 1. Company 2. Contact 3. Pic

CONTACT >----(:company_id)----- COMPANY ----(:company_id)-----< PIC

(A single company can have many contacts, and can have many pics... No relationship b/w the CONTACT and PIC tables)

HERE IS THE PHP CODE WHICH USES THE POSTGRESQL QUERY :

<?php

$app->get('/companies/{id}', function($request)
{
$db = pg_connect("host=localhost dbname=cool_db_name user=postgres")
                or die('Could not connect: ' . pg_last_error());

    $id = $request->getAttribute('id');

    $query = "

    SELECT row_to_json(t)
    FROM (
        SELECT company_id, company_name, company_street, company_city, company_state, company_zip, active_ind

        , (
        SELECT array_to_json(array_agg(row_to_json(c)))
        FROM (
            SELECT ct.contact_id, ct.contact_lname, ct.contact_fname, ct.contact_email, ct.active_ind
            FROM contact ct
            WHERE ct.company_id = c.company_id
            ORDER by ct.contact_lname
        ) c
        ) AS contacts

        , (
            SELECT array_to_json(array_agg(row_to_json(p)))
            FROM (
            SELECT p.pic_id, p.pic_location, p.active_ind
            FROM pic p
            WHERE p.company_id = c.company_id
            AND p.active_ind = 1
            ORDER by p.pic_id
        ) p
        ) AS pics

        FROM company c

        WHERE c.alive_ind = 1 AND c.company_id = " . $id . "
    ) t
    ;";


    $result = pg_query($db, $query);

    while($row = pg_fetch_assoc($result))
    {
        $data[] = $row;
    }

    if(isset($data))
    {
        header('Content-Type: application/json');
        echo json_encode($data);
    }
});

1. Again, if I run that PostgreSQL query by itself, the result is VALID JSON (below) :

{ "company_id": 1, "company_name": "Company #1", "company_street": "111 Fun Ave", "company_city": "Creve Coeur", "company_state": "MO", "company_zip": "10002", "active_ind": 1, "contacts": [{ "contact_id": 1, "contact_lname": "Figgis", "contact_fname": "Cyril", "contact_email": "[email protected]", "active_ind": 1 }, { "contact_id": 2, "contact_lname": "Kane", "contact_fname": "Lana", "contact_email": "[email protected]", "active_ind": 1 }, { "contact_id": 3, "contact_lname": "Tunt", "contact_fname": "Cheryl", "contact_email": "[email protected]", "active_ind": 1 }], "pics": [{ "pic_id": 1, "pic_location": "profile/pic_900.jpg", "active_ind": 1 }, { "pic_id": 2, "pic_location": "profile/pic_901.jpg", "active_ind": 1 }, { "pic_id": 3, "pic_location": "profile/pic_902.jpg", "active_ind": 1 }, { "pic_id": 4, "pic_location": "profile/pic_903.jpg", "active_ind": 1 }] }

(sorry for the poor formatting... it looks better in jsonlint.com)

2. ...but when I run that same query in the PHP application I get the following result (WEIRD JSON) :

[{"row_to_json":"{\"company_id\":1,\"company_name\":\"Company #1\",\"company_street\":\"111 Fun Ave\",\"company_city\":\"Creve Coeur\",\"company_state\":\"MO\",\"company_zip\":\"10002\",\"active_ind\":1,\"contacts\":[{\"contact_id\":1,\"contact_lname\":\"Figgis\",\"contact_fname\":\"Cyril\",\"contact_email\":\"[email protected]\",\"active_ind\":1},{\"contact_id\":2,\"contact_lname\":\"Kane\",\"contact_fname\":\"Lana\",\"contact_email\":\"[email protected]\",\"active_ind\":1},{\"contact_id\":3,\"contact_lname\":\"Tunt\",\"contact_fname\":\"Cheryl\",\"contact_email\":\"[email protected]\",\"active_ind\":1}],\"pics\":[{\"pic_id\":1,\"pic_location\":\"profile/pic_900.jpg\",\"active_ind\":1},{\"pic_id\":2,\"pic_location\":\"profile/pic_901.jpg\",\"active_ind\":1},{\"pic_id\":3,\"pic_location\":\"profile/pic_902.jpg\",\"active_ind\":1},{\"pic_id\":4,\"pic_location\":\"profile/pic_903.jpg\",\"active_ind\":1}]}"}]

Like I said, I'm at a loss right now. Any help would be GREATLY appreciated. Thanks!

Your problem is that you use ARRAY_AGG() around the ROW_TO_JSON() function, which breaks your json, and then you try to convert it back to json using ARRAY_TO_JSON(). You should use JSON_AGG() instead of ARRAY_TO_JSON(ARRAY_AGG()), then you won't need any dirty workarounds as you suggest in your answer — E Y
– E Y, Commented Sep 1, 2022 at 14:27

E Y · Accepted Answer · 2022-09-01 14:33:50Z

1

If someone else hits this problem and finds this question, both of the suggested answers are not an answer but a dirty workaround. The real problem lays on the query at the beginning and not on the php side.

Don't use `ARRAY_AGG(ROW_TO_JSON())`

Don't wrap any JSON function of PostgreSQL in ARRAY_AGG(), which will convert the JSON into a PostgreSQL Array and effectively will brake the JSON format escaping the quotes. A PosgreSQL array is not a JSON or a PHP array.

Use instead `JSON_AGG()`

The right aggregation function of JSON objects returned by any PostgreSQL JSON function like ROW_TO_JSON, JSON_BUILD_OBJECT etc, is done using JSON_AGG() function.

answered Sep 1, 2022 at 14:33

E Y

1,18712 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick Bull · Accepted Answer · 2016-08-06 22:57:14Z

0

So, using that last escaped data format, you could do:

$noSlahes = str_replace("\", "", $yourEscapedJsonString);

This removes the slashes, replacing them with nothing. Now:

$jsonObject = json_decode($noSlahes);

Then, select rowToJson for it to be identical to your properly formatted JSON:

echo $jsonObject->row_to_json;

answered Aug 6, 2016 at 22:57

Nick Bull

9,9667 gold badges39 silver badges68 bronze badges

3 Comments

bjones1831 Over a year ago

I ended up going with the solution below... But your json_decode() tip put me on the array_values() track. I'll mark your answer as correct if you incorporate my modifications.

bjones1831 Over a year ago

And thank you for the help with this! I've been picking at this since yesterday afternoon!

Nick Bull Over a year ago

Haha, you've solved it! Mark your own as the correct answer :)

bjones1831 · Accepted Answer · 2016-08-07 00:31:19Z

-2

header('Content-Type: application/json');
$noSlashes = str_replace("\\","",$data); /* no need for json_encode() */
$hope = array_values($noSlashes[0]);
foreach($hope as $key => $value)
{
     print $value;
}

answered Aug 7, 2016 at 0:31

bjones1831

2855 silver badges13 bronze badges

1 Comment

Jonathan Lam Over a year ago

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations!

Collectives™ on Stack Overflow

Display PostgreSQL JSON in PHP

3 Answers 3

Don't use `ARRAY_AGG(ROW_TO_JSON())`

Use instead `JSON_AGG()`

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Don't use ARRAY_AGG(ROW_TO_JSON())

Use instead JSON_AGG()

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

Don't use `ARRAY_AGG(ROW_TO_JSON())`

Use instead `JSON_AGG()`