CODEWITHSUNDEEP
sqlpostgresql
Jordash
Jordash

Reputation: 3103

PostgreSQL select specific items from JSONB array

I have a table like this:

orders

order_number   products
1234           [{"upc":2434354, "title":"Widget"}]
4321           [{"upc":6434556, "title":"Shorts"}, {"upc":54346, title: "Shirt"}]

I want to generate an output like this

[
{order_number: 1234, upc: 2434354},
{order_number: 4321, upc: 6434556}
]

As you can see I pull out the UPC from the array (targeting the first element in the array only).

Note that products if of type jsonb

I tried this:

SELECT jsonb_array_elements(products)[0]->>upc FROM orders ORDER BY created DESC LIMIT 10

But it gives a syntax error, I'm not sure exactly how to do array traversal in PostgreSQL

Also there is the possibility that products may be an empty array []

Upvotes: 2

Views: 57

Answers (1)

S-Man
S-Man

Reputation: 23766

demo:db<>fiddle

SELECT
    jsonb_agg(
         jsonb_build_object(
             'order_number', order_number, 
             'upc', products -> 0 -> 'upc'
         )
    )
FROM
    orders
  1. products -> 0 -> 'upc' gets the upc value of the first array element
  2. jsonb_build_object() builds one JSON object per record containing the order_number and upc value
  3. jsonb_agg() aggregates these JSON objects into one JSON array

Upvotes: 4

Related Questions