JSON queries in PostgreSQL 9.6

Question

presumed the following data:

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"                +| 
    |       }                                   +| 
    |     }                                     +| 
    |   } ]                                     +| 
    | }                                          | 

I want to get the 'OfferPurchaseOrder'; therefore the following SELECT was used:

 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"             +
    |                  |   }

It looks as if works like a charm, only one thing: I want to integrate with Hibernate, so the colum should be named 'orders' instead of 'o' (as it was in the initial select); otherwise Hibernate will not be able to map the things properly. Aside of this, the 'reduced' JSON-list should be in there, so the desired result should look like this:

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

Any hints?

Thx and regards el subcomandante

Answer 1

If you can move to jsonb type than query can looks like:

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
;

However, if you can't use jsonb just cast text to 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
;