PostgreSQL 9.6 中的 JSON 查询

Question

假定以下数据:

select * from orderlist ;
 id |                   orders                   | orderenvelope_id 
----+--------------------------------------------+------------------
 14 | {                                         +|               13
    |   "orders" : [ {                          +| 
    |     "orderType" : "OfferPurchaseOrder",   +| 
    |     "duration" : 1494413009450,           +| 
    |     "currencyCode" : "EUR"                +| 
    |   }, {                                    +| 
    |     "orderType" : "CustomerCreationOrder",+| 
    |     "customerData" : {                    +| 
    |       "customerType" : "PERSONAL_ACCOUNT",+| 
    |       "contactData" : {                   +| 
    |         "contactQuality" : "VALID",       +| 
    |         "firstName" : "Peter",            +| 
    |         "lastName" : "Pan"                +| 
    |       }                                   +| 
    |     }                                     +| 
    |   } ]                                     +| 
    | }                                          | 

我想获得'OfferPurchaseOrder'；因此使用了以下 SELECT:

 select id, orderenvelope_id, o from orderlist list, json_array_elements(list.orders->'orders') as o where o->>'orderType' = 'OfferPurchaseOrder';

     id | orderenvelope_id |                    o                    
----+------------------+-----------------------------------------
 14 |               13 | {                                      +
    |                  |     "orderType" : "OfferPurchaseOrder",+
    |                  |     "duration" : 1494413009450,        +
    |                  |     "currencyCode" : "EUR"             +
    |                  |   }

看起来好像很有魅力，只有一件事:我想与 Hibernate 集成，所以该列应该命名为“orders”而不是“o”(就像在初始选择中那样)；否则 Hibernate 将无法正确映射这些东西。除此之外，“简化的”JSON 列表应该在那里，因此所需的结果应该如下所示:

 id | orderenvelope_id |                    orders              |            
----+------------------+----------------------------------------+
 14 |               13 | "orders" : [{                          +
    |                  |     "orderType" : "OfferPurchaseOrder",+
    |                  |     "duration" : 1494413009450,        +
    |                  |     "currencyCode" : "EUR"             +
    |                  |   }                                    +
    |                  |]

有什么提示吗？

谢谢和问候副指挥官

Answer 1

如果你可以移动到 jsonb 类型而不是查询可以看起来像:

WITH x AS (
   SELECT id, orderenvelope_id, o
   FROM orderlist list, jsonb_array_elements(list.orders->'orders') as o
   WHERE o->>'orderType' = 'OfferPurchaseOrder'
)
SELECT id, orderenvelope_id, jsonb_set('{}'::jsonb, '{orders}'::text[], jsonb_agg(o))
FROM x
GROUP BY 1,2
;

但是，如果您不能使用 jsonb，只需将文本转换为 json:

WITH x AS (
   SELECT id, orderenvelope_id, o
   FROM orderlist list, json_array_elements(list.orders->'orders') as o
   WHERE o->>'orderType' = 'OfferPurchaseOrder'
)
SELECT id, orderenvelope_id, ('{"orders": ' || json_agg(o) ||'}')::json
FROM x
GROUP BY 1,2
;