- How can I select a specific element in "opts" array?
Use the index of the element number as instructed in the manual. For longer paths, the path notation is shorter:
SELECT metadata -> 'options' -> 'opts' -> 0 AS elem0
, metadata #> '{options, opts, 0}' AS elem0_path
FROM product;
Gets the nth element, like the question title asks (JSON array index starts with 0). But your example indicates you actually want the element with "value":"blue"
. That's not as trivial. You could unnest the nested JSON array with jsonb_array_elements()
in a LATERAL
join and filter the one you want.
SELECT opt AS elem_blue
-- , metadata #> '{options, opts}' -> (arr.ord::int - 1) AS elem_blue2
FROM product
, jsonb_array_elements(metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
WHERE opt ->> 'value' = 'blue';
Returns nothing if no matching element is found.
WITH ORDNALITY
and elem_blue2
are not needed here, but demonstrate a technique we are going to use in the next step.
More explanation:
2.how to update the "qty" (subtract from its current qty) when one or more of them is sold?
Since Postgres 9.5, there is jsonb_set()
. To update the value of the qty
key of the first element:
UPDATE product
SET metadata = jsonb_set(metadata, '{options, opts, 0, qty}', '"29"', false)
WHERE ... -- some filter
Aside: Any reason your integer numbers in qty
are stored as strings ("30"
), not as numbers (30
)?
To update the "blue" element, we determine the array index with the technique demonstrated above, plus some UPDATE
magic to make the it fully dynamic:
UPDATE product p
SET metadata = jsonb_set(p.metadata, path, qty, false)
FROM product p1
, LATERAL ( -- move computations to subquery
SELECT ARRAY['options', 'opts', (ord - 1)::text, 'qty'] AS path -- fix off-by-one
, to_jsonb((opt ->> 'qty')::int - 1) AS qty -- subtract here!
FROM jsonb_array_elements(p1.metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
WHERE opt ->> 'value' = 'blue'
-- AND ... -- more filters
-- FOR UPDATE -- see below
) opt
WHERE p1.product_id = p.product_id -- use PK for match
The last query writes a number to "qty" ('9'
), not a string ('"9"'
), assuming you fixed that as commented.
We need to list the table product
in the FROM
clause another time to allow the LATERAL
join (which wouldn't be possible otherwise) - and self-join to it.
Note a tiny race condition. Under heavy concurrent write load, you might want to add a lock clause to the subquery (FOR UPDATE
) to prevent other transactions from changing the row between the inner SELECT
and the outer UPDATE
. See:
But you shouldn't, really. As you can see, jsonb
(or any document type for that matter) is ill-suited for regular updates to single attributes. That's cumbersome and comparatively expensive. A new row with a new version of the complete document has to be written every time. Use a normalized DB design instead, where only a comparatively very small row has to be rewritten and other parts as well as indexes remain untouched.
If only the quantity is updated regularly, you might move that to a 1:n table and merge it into the JSON document in a VIEW
or MATERIALIZED VIEW
. The redesign is beyond the scope of this question.