将 JSON 解压缩为平面格式

Question

我有类似于以下内容的JSON：

{
"ANNOTATIONS": [
{
"Label": "CommingledProduct",
"Text": "NBP"
},
{
"Label": "CommingledVenue",
"Text": "OTC"
}
]
}

我需要将其解压缩到一个平面表中，其中包含与注释标签匹配的列。 因此，基于上述 json 的列变为：

• comingled_product
• comingled_venue

JSON 来自源表中的 json 字段，并被解压缩到另一个表中。

我知道我可以按如下方式编码：

INSERT INTO my_target_table (comingled_product, comingled_venue)
SELECT
payload->'ANNOTATIONS'->0->>'Text',
payload->'ANNOTATIONS'->1->>'Text'
FROM my_source_table;

但是，我宁愿不使用注释的序数。我更愿意使用一些语法来镜像下面的伪代码：

INSERT INTO my_target_table (comingled_product, comingled_venue)
SELECT
payload->'ANNOTATIONS'->'label="ComingledProduct"'->>'Text',
payload->'ANNOTATIONS'->'label="ComingledVenueID"'->>'Text'
FROM my_source_table;

谁能告诉我我试图实现的事情是否可行以及如何做到这一点？ 我在示例中包含的两个注释不止两个，因此任何涉及多个联接的内容都可能是不行的。

使用 PostGres 10.7

Answer 1

demo：db<>fiddle

WITH cte AS (
SELECT 
elems.value
FROM 
my_source_table,
json_array_elements(payload -> 'ANNOTATIONS') elems
)
SELECT 
(SELECT value ->> 'Text' FROM cte WHERE value ->> 'Label' = 'CommingledProduct'),
(SELECT value ->> 'Text' FROM cte WHERE value ->> 'Label' = 'CommingledVenue')

1. 将数组扩展到每个数组元素的一行，并将此结果存储到 CTE 中以供进一步使用
2. 此结果可用于查询预期值(无需执行两次扩展)

---

可能会快一点：

演示：数据库<>小提琴

SELECT
payload,
MIN(the_text) FILTER (WHERE label = 'CommingledProduct'),
MIN(the_text) FILTER (WHERE label = 'CommingledVenue')
FROM (
SELECT 
payload::text AS payload,
elems ->> 'Label' AS label,
elems ->> 'Text' AS the_text
FROM 
my_source_table,
json_array_elements(payload -> 'ANNOTATIONS') elems
) s
GROUP BY payload

Answer 2

@S-Man的答案很棒，你应该在postgres 10.7中使用它。 json_path将在 postgres 12 中添加，这将允许您执行更接近伪代码的操作，但只能使用 jsonb(不是 json)：

INSERT INTO my_target_table (comingled_product, comingled_venue)
SELECT jsonb_path_query(payload, 
'$.ANNOTATIONS[*] ? (@.Label == "CommingledProduct")')->>'Text', 
jsonb_path_query(payload, 
'$.ANNOTATIONS[*] ? (@.Label == "CommingledVenue")')->>'Text' 
FROM my_source_table;

jsonb_path_query语法需要一点时间来弄清楚，但它基本上是返回 ANNOTATIONS 数组的元素，其中 Label 等于 CommingledProduct 或 CommingledVenue。 jsonb_path_query返回一个 jsonb 对象，因此我们可以使用 ->> 运算符从对象中获取 'Text' 的值。