json - 在 Postgresql 中从 JSON 中获取数据

Question

我是 Postgresql 中 JSON 查询的新手。以下是我到目前为止的练习

INSERT INTO property_graph(node,edges_property)
SELECT 'E','{
 "Owner" : 
  [
     {"Edgeid":1,  "Weight": 10, "Active":1, "OutgoingVertexid": "A", "IncomingVertexid": ""},
     {"Edgeid":2,  "Weight": 20, "Active":1, "OutgoingVertexid": "B", "IncomingVertexid": ""}
  ],
  "Supporter" : 
  [
     {"Edgeid":3,  "Weight": 30, "Active":0, "OutgoingVertexid": "C", "IncomingVertexid": ""},
     {"Edgeid":4,  "Weight": 40, "Active":0, "OutgoingVertexid": "D", "IncomingVertexid": ""}
  ]
}'

我正在尝试以以下格式获取数据：

我尝试使用以下查询但没有得到正确的输出：

SELECT node,
edges_property::JSON -> 'Owner' -> 'OutgoingVertexid' AS Owner_OutgoingVertexids,
edges_property::JSON -> 'Supporter' -> 'OutgoingVertexid' AS Supporter_OutgoingVertexids
FROM property_graph

请帮我解决我的查询，如果我存储错误的 json 格式，还建议我。提前致谢。

标签： jsonpostgresql

Answer 1

您可以将零件edges_property::JSON -> 'Owner'和-> 'OutgoingVertexid'. 将第二步中的第二步保留为->>运算符并包含string_agg()函数：

WITH property_graph2 AS
(
SELECT node, 
       edges_property::JSON -> 'Owner' AS Owner_OutgoingVertexids, 
       edges_property::JSON -> 'Supporter' AS Supporter_OutgoingVertexids 
  FROM property_graph
)
SELECT node, 
       string_agg(distinct j1.value ->> 'OutgoingVertexid',',') AS Owner_OutgoingVertexids,
       string_agg(distinct j2.value ->> 'OutgoingVertexid',',') AS Supporter_OutgoingVertexids
  FROM property_graph2
 CROSS JOIN json_array_elements(Owner_OutgoingVertexids) j1 
 CROSS JOIN json_array_elements(Supporter_OutgoingVertexids) j2 
 GROUP BY node

对于您的情况，包含json_each()功能的替代选项可能如下：

SELECT node, 
       string_agg(Owner_OutgoingVertexids,',') AS Owner_OutgoingVertexids,
       string_agg(Supporter_OutgoingVertexids,',') AS Supporter_OutgoingVertexids
  FROM
  (
   SELECT CASE 
          WHEN k='Owner' THEN 
              json_array_elements(v)->>'OutgoingVertexid'
           END AS Owner_OutgoingVertexids, 
          CASE 
          WHEN k='Supporter' THEN 
              json_array_elements(v)->>'OutgoingVertexid'
           END AS Supporter_OutgoingVertexids, 
          t.*,
          js.*
     FROM property_graph t
    CROSS JOIN json_each(edges_property::json) AS js(k,v)
   ) q 
 GROUP BY node

Demo