PostgreSQL and nested json objects

Question

I need to build json object in a such structure:

{
    "id":1, 
    "name": "Jessica", //other top-level key-values
    "add_info": {
        "first_object":{"date":"2017-04-17","id":1},
        "second_object":{"date":"2017-04-17","id":1} //etc.
    }
}

Unfortunately I got confused on that big amount of a great but not exampled PostgreSQL json-functions and can't figure out the way how to achieve this better. I tried this:

SELECT row_to_json(t, TRUE)
FROM (
       SELECT
         id, name
         , (
             (
               SELECT row_to_json(b) AS first_object
               FROM (
                      SELECT *
                      FROM table1
                      WHERE client_id = 1
                    ) b
             ),
             (
               SELECT row_to_json(b) AS second_object
               FROM (
                      SELECT *
                      FROM table2
                      WHERE client_id = 1
                    ) b
             )
           ) AS add_info

       FROM main_table
       WHERE id = 1
     ) t;

But I'm getting this:

{
    "id":1, 
    "name": "Jessica", //other top-level key-values
    "add_info": {
        "f1":{"date":"2017-04-17","id":1},
        "f2":{"date":"2017-04-17","id":1} //etc.
    }
}

f1, f2!! Whaat??!

Seems like I can do something to give them names:

SELECT row_to_json(t, TRUE)
FROM (
       SELECT
         id, name
         , (
             (SELECT row_to_json(first_row) first_row
              FROM
                (
                  SELECT row_to_json(b) AS first_object
                  FROM (
                         SELECT *
                         FROM first_table
                         WHERE client_id = 1
                       ) b
                ) AS first_row
             ),
             (SELECT row_to_json(second_row) second_row
              FROM
                (
                  SELECT row_to_json(b) AS second_object
                  FROM (
                         SELECT *
                         FROM second_table
                         WHERE client_id = 1
                       ) b
                ) AS second_row
             )
           ) AS add_info

       FROM main_table
       WHERE id = 1
     ) t;

But this gives an extra object:

{
    "id":1,
    "name":"CRED", 
    "add_info":{
        "f1":{"first_object":{"date":"2017-04-17","id":1}},
        "f2":{"second_object":{"date":"2017-04-17","id":1}}
    }
}

Can I fix this?

Laurenz Albe · Accepted Answer · 2017-04-18 10:06:32Z

2

You could use a common table expression:

WITH add_info(first_object, second_object) AS
     (SELECT
        (SELECT row_to_json(table1)
                FROM table1
                WHERE id = 1
        ),
        (SELECT row_to_json(table2)
                FROM table2
                WHERE id = 1
        )
     )
SELECT row_to_json(t, TRUE)
FROM (
       SELECT
         id, name, add_info
       FROM main_table CROSS JOIN add_info
       WHERE id = 1
     ) t;

answered Apr 18, 2017 at 10:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Frankie Drake Over a year ago

Seems like it's what I need. Tried to avoid multiple select's, but it seems not achievable now...

Collectives™ on Stack Overflow

PostgreSQL and nested json objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related