How to append string to each json keys while selecting json keys using jsonb_object_keys or json_object_keys in postgres?

huangapple go评论52阅读模式
英文:

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

现在我希望输出如下:

How to append string to each json keys while selecting json keys using jsonb_object_keys or json_object_keys in postgres?

英文:

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

How to append string to each json keys while selecting json keys using jsonb_object_keys or json_object_keys in postgres?

答案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

huangapple
  • 本文由 发表于 2023年3月12日 19:20:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/75712753.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定