英文:
How to append string to each json keys while selecting json keys using jsonb_object_keys or json_object_keys in postgres?
问题
我有一个名为data_table
的表
CREATE TABLE data_table(d_id int, d_json jsonb, c_id int, t_id int);
表中的JSON数据如下:
INSERT INTO data_table
VALUES (1, '{
"key11": "value11",
"key21": "value21",
"key31": "value31",
"key41": "value41",
"key51": "value51",
"key61": "value61"
}', 1, 1);
现在我希望输出如下:
英文:
I have table called data_table
CREATE TABLE data_table(d_id int, d_json jsonb, c_id int, t_id int);
And json data in table like:
INSERT INTO data_table
VALUES (1,'{
"key11": "value11",
"key21": "value21",
"key31": "value31",
"key41": "value41",
"key51": "value51",
"key61": "value61"
}',1,1);
Now I want output as
答案1
得分: 1
尝试在Select查询中使用CONCAT_WS
。
CONCAT_WS
是一个Postgres
函数,代表"使用分隔符连接"。它接受多个字符串作为输入,并使用指定的分隔符将它们连接成一个单一的字符串。函数的第一个参数是分隔符,后面是要连接的字符串。
以下是代码:
SELECT
CONCAT_WS('', STRING_AGG(DISTINCT CONCAT(json_keys.json_column_key, e' TEXT'), e',\n\t'), '') AS json_column_list
FROM
(SELECT jsonb_object_keys(d_json) AS json_column_key FROM data_table) AS json_keys
英文:
Try with CONCAT_WS
in Select Query.
CONCAT_WS
is a Postgres
function that stands for "Concatenate With Separator". It takes multiple strings as input and concatenates them into a single string using a specified separator between each string. The first argument of the function is the separator, followed by the strings to be concatenated.
Here is code:
SELECT
CONCAT_WS('', STRING_AGG(DISTINCT CONCAT(json_keys.json_column_key, e' TEXT'), e',\n\t'), '') AS json_column_list
FROM
(SELECT jsonb_object_keys(d_json) AS json_column_key FROM data_table) AS json_keys
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论