如何在PostgreSQL 9.3深层查询和索引嵌套多层的JSON数据？

概述在PostgreSQL 9.3中,我存储了一些相当复杂的 JSON对象,其中数组嵌套在数组中.此片段不是真实数据,但说明了相同的概念： { "customerId" : "12345", "orders" : [{ "orderId" : "54321", "lineItems" : [{ "productId" : "abc", 在Postgresql 9.3中,我存储了一些相当复杂的 JSON对象,其中数组嵌套在数组中.此片段不是真实数据,但说明了相同的概念：

{   "customerID" : "12345","orders" : [{      "orderID" : "54321","lineItems" : [{         "productID" : "abc","qty" : 3      },{         "productID" : "def","qty" : 1      }]   }}

我希望SQL查询能够对lineItem对象进行 *** 作…不仅在这个单一的JsON结构中,而且在该表列中的所有JsON对象中.例如,一个SQL查询返回所有不同的productID,以及它们的总销售数量总和.为了防止这样的查询花了一整天,我可能想要一个关于lineItem或其子字段的索引.

使用this StackOverflow question,我想出了如何编写一个有效的查询：

SELECT   line_item->>'productID' AS product_ID,SUM(CAST(line_item->>'qty' AS INTEGER)) AS qty_soldFROM   my_table,Json_array_elements(my_table.my_Json_column->'orders') AS order,Json_array_elements(order->'lineItems') AS line_itemGROUP BY product_ID;

但是,最初的StackOverflow问题处理的是仅嵌套一层而不是两层的数据.我扩展了相同的概念(即FROM子句中的“横向连接”),通过添加额外的横向连接来深入一级.但是,我不确定这是否是最好的方法,因此我的问题的第一部分是：查询JsON对象中任意数量级别的JsON数据的最佳方法是什么？

对于第二部分,在这样的嵌套数据上创建索引,this StackOverflow question再次处理仅嵌套一层深度的数据.然而,我只是完全失去了,我的头脑游泳试图想到我将如何应用于更深层次的水平.任何人都可以提供一个明确的方法来索引至少两个级别的数据,如上面的lineItems？

要处理无限递归问题,您需要使用 recursive CTE来 *** 作每个表行中的每个Json元素：

WITH RECURSIVEraw_Json as (  SELECT     *  FROM  (VALUES   (1,'{  "customerID": "12345","orders": [    {      "orderID": "54321","lineItems": [        {          "productID": "abc","qty": 3        },{          "productID": "def","qty": 1        }      ]    }  ]}'::Json),(2,'{  "customerID": "678910","artibitraryLevel": {    "orders": [      {        "orderID": "55345","lineItems": [          {            "productID": "abc","qty": 3          },{            "productID": "ghi","qty": 10          }        ]      }    ]  }}'::Json)) a(ID,sample_Json)),Json_recursive as (  SELECT    a.ID,b.k,b.v,b.Json_type,case when b.Json_type = 'object' and not (b.v->>'customerID') is null then b.v->>'customerID' else a.customer_ID end customer_ID,--track any arbitrary ID when iterating through Json graph    case when b.Json_type = 'object' and not (b.v->>'orderID') is null then b.v->>'orderID' else a.order_ID end order_ID,case when b.Json_type = 'object' and not (b.v->>'productID') is null then b.v->>'productID' else a.product_ID end product_ID  FROM    (      SELECT        ID,sample_Json v,case left(sample_Json::text,1)          when '[' then 'array'          when '{' then 'object'          else 'scalar'        end Json_type,--because choice of Json accessor function depends on this,and for some reason postgres has no built in function to get this value        sample_Json->>'customerID' customer_ID,sample_Json->>'orderID' order_ID,sample_Json->>'productID' product_ID      FROM        raw_Json    ) a    CROSS JOIN LAteraL (      SELECT        b.k,case left(b.v::text,1)          when '[' then 'array'          when '{' then 'object'          else 'scalar'        end Json_type      FROM        Json_each(case Json_type when 'object' then a.v else null end ) b(k,v) --get key value pairs for indivIDual elements if we are dealing with standard object     UNION ALL      SELECT        null::text k,c.v,case left(c.v::text,1)          when '[' then 'array'          when '{' then 'object'          else 'scalar'        end Json_type      FROM        Json_array_elements(case Json_type when 'array' then a.v else null end) c(v) --if we have an array,just get the elements and use parent key    ) bUNION ALL --recursive term    SELECT    a.ID,case when b.Json_type = 'object' and not (b.v->>'orderID') is null then b.v->>'orderID' else a.order_ID end order_ID,case when b.Json_type = 'object' and not (b.v->>'productID') is null then b.v->>'productID' else a.product_ID end product_ID  FROM    Json_recursive a    CROSS JOIN LAteraL (      SELECT        b.k,v)     UNION ALL      SELECT        a.k,1)          when '[' then 'array'          when '{' then 'object'          else 'scalar'        end Json_type      FROM        Json_array_elements(case Json_type when 'array' then a.v else null end) c(v)    ) b)

然后你可以用任意ID加“qty”…

SELECT  customer_ID,sum(v::text::integer)FROM  Json_recursiveWHERE  k = 'qty'GROUP BY   customer_ID

或者您可以获取“lineItem”对象并根据需要 *** 作它们：

SELECT   *FROM   Json_recursive WHERE  k = 'lineItems' and Json_type = 'object'

至于索引,您可以将递归查询调整为一个函数,该函数返回原始表的每一行中每个Json对象的唯一键,然后在Json列上创建一个函数索引：

SELECT  array_agg(disTINCT k)FROM  Json_recursiveWHERE  not k is null

总结

以上是内存溢出为你收集整理的如何在PostgreSQL 9.3深层查询和索引嵌套多层的JSON数据？全部内容，希望文章能够帮你解决如何在PostgreSQL 9.3深层查询和索引嵌套多层的JSON数据？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。