如何将jsonb [Postresql]中的元素转换为数组

Question

我需要将jsonb列中的一些元素转换为数组

What I have:

{"a": {
"b": "2022-11-03",
"c": "321321",
"d": "213321"
}
}

我需要什么:'

{"a": [
{
"b": "2022-11-03",
"c": "321321",
"d": "213321"
}
]
}

Answer 1

您可以使用jsonb_set():

SELECT jsonb_set(the_column, '{a}', jsonb_build_array(the_column -> 'a'))
FROM the_table

Answer 2

使用"json_agg"函数。

select json_agg(DATA) from table_json

,例子:db&lt的在小提琴

Answer 3

Using function from here JSON函数和the SQL/JSON路径语言。使用路径语言分解原始对象，然后使用jsonb_build_object重新构建新形式。

SELECT
jsonb_build_object(
(jsonb_path_query_array('{"a": {"b": "2022-11-03","c": "321321","d": "213321"}}', '$.keyvalue()') -> 0 -> 'key') ->> 0, 
(jsonb_path_query_array('{"a": {"b": "2022-11-03","c": "321321","d": "213321"}}', '$.keyvalue()') -> 0 -> 'value' 
|| '[]'::jsonb));
jsonb_build_object                     
------------------------------------------------------------
{"a": [{"b": "2022-11-03", "c": "321321", "d": "213321"}]}