PostgreSQL: select from field with json format

Question

表有列，名为" config ";内容如下:

{
"A":{
"B":[
{"name":"someName","version":"someVersion"},
{"name":"someName","version":"someVersion"}
]
}
}

任务是选择所有的名称和版本值。输出是预期的选择，包含两列:name和value。

我成功选择B的内容:

select config::json -> 'A' -> 'B' as B
from my_table;

但是当我试图做这样的事情时:

select config::json -> 'A' -> 'B' ->> 'name' as name,
config::json -> 'A' -> 'B' ->> 'version' as version
from my_table;

我接收空列的选择

Answer 1

如果数组的大小是固定的，你只需要告诉数组的哪个元素你想要检索，例如:

SELECT config->'A'->'B'->0->>'name' AS name,
config->'A'->'B'->0->>'version' AS version
FROM my_table;

但是当你的数组包含多个元素时，在子查询或CTE中使用jsonb_array_elements函数，并在外部查询中单独解析每个元素，例如:

SELECT rec->>'name', rec->>'version'
FROM (SELECT jsonb_array_elements(config->'A'->'B') 
FROM my_table) j (rec);

Demo:db<>fiddle

Answer 2

首先，您应该使用jsonb数据类型而不是json，请参阅文档:

一般来说，大多数应用程序应该倾向于将JSON数据存储为Jsonb，除非有非常特殊的需求，比如遗留关于对象键排序的假设。

使用jsonb，您可以执行以下操作:

SELECT DISTINCT ON (c) c->'name' AS name, c->'version' AS version
FROM my_table
CROSS JOIN LATERAL jsonb_path_query(config :: jsonb, '$.** ? (exists(@.name))') AS c

Answer 3

dbfiddle

select e.value ->> 'name', e.value ->> 'version'
from 
my_table cross join json_array_elements(config::json -> 'A' -> 'B') e