PostgreSQL和嵌套JSON对象
问题描述:
我需要建立JSON对象在这样的结构:PostgreSQL和嵌套JSON对象
{
"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.
}
}
不幸的是我糊涂了上一个伟大但并非所示例PostgreSQL的JSON-函数大额和想不通如何实现这个更好的方式。 我尝试这样做:
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;
但我发现了这一点:
{
"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 ??!
好像我可以做一些事情,给他们的名字:
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;
但是,这给出了一个额外的对象:
{
"id":1,
"name":"CRED",
"add_info":{
"f1":{"first_object":{"date":"2017-04-17","id":1}},
"f2":{"second_object":{"date":"2017-04-17","id":1}}
}
}
我能解决这个问题?
答
你可以使用一个公用表表达式:
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;
好像这是我需要什么。试图避免多个“选择”,但现在似乎无法实现... – Daria