PostgreSQL:将jsonb数组列中的值显示为CSV [英] Postgresql: display values in jsonb array column as CSV
问题描述
我有一个用于存储值数组的jsonb列:
I have a jsonb column that is used to store an array of value:
["value1", "value2", "value3"]
我想将该数组中的值与另一列的值连接起来,例如而不是:
I would like to concatenate the values inside that array with the value of another column, such as instead of having:
string column | jsonb column
--------------+-------------------------------
mystring | ["value1", "value2", "value3"]
我可以拥有:
string + jsonb
------------------------------------
mystring, value1, value2, value3
我天真地尝试使用jsonb_array_elements无济于事。
I naively tried using jsonb_array_elements to no avail.
SELECT string_column || ', ' || jsonbvalues
FROM mytable, jsonb_array_elements(mytable.jsonb_column) as jsonbvalues
有人可以指出
推荐答案
函数 json_array_elements()
返回设置是正确的语法,因此您需要一个汇总函数。
如果聚合所在的列是唯一的,聚合将是正确的。
The function json_array_elements()
returns set, so you need an aggregate function.
An aggregation will be correct if the column it is grouped by is unique.
下面的查询假定 string_column
是唯一的。
The query below assumes that string_column
is unique.
SELECT format('%s, %s', string_column, string_agg(jsonbvalues, ', ')) result
FROM (
SELECT string_column, jsonbvalues
FROM mytable, jsonb_array_elements_text(mytable.jsonb_column) as jsonbvalues
) sub
GROUP BY string_column;
result
----------------------------------
mystring, value1, value2, value3
(1 row)
如果 string_column
不是唯一的,例如:
If string_column
is not unique, e.g.:
SELECT * FROM mytable;
string_column | jsonb_column
---------------+--------------------------------
mystring | ["value1", "value2", "value3"]
mystring | ["value4", "value5", "value6"]
(2 rows)
您可以使用 row_number()
来标识原始行:
you can use row_number()
to identify original rows:
SELECT format('%s, %s', string_column, string_agg(jsonbvalues, ', ')) result
FROM (
SELECT rn, string_column, jsonbvalues
FROM (
SELECT *, row_number() over () as rn
FROM mytable
) t,
jsonb_array_elements_text(jsonb_column) as jsonbvalues
) sub
GROUP BY rn, string_column;
result
----------------------------------
mystring, value1, value2, value3
mystring, value4, value5, value6
(2 rows)
这篇关于PostgreSQL:将jsonb数组列中的值显示为CSV的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!