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?
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;