Postgres JSONB, query first appearance of inner field

Question

Trying to query product_id and inner JSONB value "quantity_on_hand" with "unit:one". Below is example table

Products table

| product_id | data         |
| --------   | -------------|
| 00445      |  {"storage"...}| - rest of data specified right below
{
  "storage": [
    {
      "unit": "one",
      "quantity": 3,
    },
    {
      "unit": "two",
      "quantity": 2,
    }
}

I found a query:

SELECT product_id, e -> 'quantity' as quant 
FROM Products t, 
     jsonb_array_elements(t.value->'storage') e 
WHERE (product_id) IN (('00445'));

The query returns following output:

product_id | quant 
00445      | 3  
00445      | 2

Please advise how to set rule: "quantity_on_hand" with "unit:one" to return only:

product_id | quant 00445 | 3

Thanks

Answer 1

You can add a clause for filtering the result of the jsonb_array_elements to only include elements where the JSON key "unit"'s value is "one":

SELECT product_id,
       e -> 'quantity' AS quant
  FROM Products t,
       JSONB_ARRAY_ELEMENTS(t.value -> 'storage') e
 WHERE (product_id) IN (('00445'))
   AND e ->> 'unit' = 'one';

This should give:

 product_id | quant
------------+-------
 1          | 3
(1 row)

See https://www.postgresql.org/docs/14/functions-json.html for more information on JSONB operators and functions in Postgres.