POSTGRESQL: How to query nested JSONB object to get a filtered JSONB object?

Question

Input Json

{
"orderId": "order1",
"fulfillerId":"ful1",
"orderDetailsUrl":"het",
"items":[{
        "decorationTechnology":"laserEngraving",
        "itemId":"item1",
        "productName":"Test Sku for Oracle testing"
    },
    {
        "decorationTechnology":"laserEngraving",
        "itemId":"item2",
        "productName":"Test Sku for Oracle testing"
    }
  ]
}

Expected Output JSON

{
   "fulfillerIds":[
      "ful1"
   ],
   "orderIds":[
      "order1"
   ],
   "itemIds":[
       "item1",
        "item2"
   ]
}

Need to form above expected JSON tried to use row_to_json() and jsonb_array_elements() but couldn't get the expected output

Which Postgres version are you using?

user330315
– user330315

2020-11-13 14:49:52 +00:00
Commented Nov 13, 2020 at 14:49 — user330315
– user330315, Commented Nov 13, 2020 at 14:49
version 11.8 @a_horse_with_no_name

Aagam Doshi
– Aagam Doshi

2020-11-17 04:59:18 +00:00
Commented Nov 17, 2020 at 4:59 — Aagam Doshi
– Aagam Doshi, Commented Nov 17, 2020 at 4:59

user330315 · Accepted Answer · 2020-11-13 15:09:02Z

1

If you are using Postgres 12 or later, you can use SQL/JSON path queries:

select jsonb_build_object(
         'orderIds', jsonb_path_query_array(the_column, '$.orderId'), 
         'fulfillerIds', jsonb_path_query_array(the_column, '$.fulfillerId'),
         'itemIds', jsonb_path_query_array(the_column, '$.items[*].itemId')
       ) as filtered
from the_table;

answered Nov 13, 2020 at 15:09

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Aagam Doshi Over a year ago

Since I am using version 11.8 this might not work for me @a_horse_with_no_name

SneakyPie · Accepted Answer · 2020-11-13 16:29:39Z

1

With earlier versions it works with concatenation and building arrays:

WITH t1 AS
    (
        SELECT '{ "orderId": "order1", "fulfillerId":"ful1", "orderDetailsUrl":"het", "items":[{ "decorationTechnology":"laserEngraving", "itemId":"item1", "productName":"Test Sku for Oracle testing" }, { "decorationTechnology":"laserEngraving", "itemId":"item2", "productName":"Test Sku for Oracle testing" } ] } '::jsonb AS field
    )
SELECT jsonb_build_object('orderId', json_build_array (field->>'orderId')) || jsonb_build_object('fulfillerId', json_build_array (field->>'fulfillerId')) || jsonb_build_object('orderDetailsUrl', json_build_array (field->>'orderDetailsUrl')) AS jsonb_result
FROM t1
;

answered Nov 13, 2020 at 16:29

SneakyPie

132 bronze badges

Collectives™ on Stack Overflow

POSTGRESQL: How to query nested JSONB object to get a filtered JSONB object?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related