Update nth element of array using a WHERE clause

I have a product table with a jsonb column named 'metadata' in a PostgreSQL 10 database. It's my first time working with documents and Postgres. jsonb values look something like this:

{
"name" : "l33t shirt",
"price" : "1200",
"quantity": "60",
"options" :
{
"type" : "radio",
"title" : "color",
"opts" : [
{"value" : "red" , "price" : "-100" , "qty" : "30" },
{"value" : "blue" , "price" : "+200" , "qty" : "10" },
{"value" : "green" , "price" : "+300" , "qty" : "20" }
]
}
}

Two questions:

1. How can I select a specific element in the "opts" array?

select metadata->'options'->'opts'->(element here) from product
where  metadata->'options'->'opts' @> '[{"value" : "blue"}]'

1. How to update the "qty" (subtract from its current "qty") when one or more of them is sold?

Further links to guides / notes are appreciated.

• 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:

• PostgreSQL unnest() with element number

• Query for element of array in JSON column

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:

• Postgres UPDATE ... LIMIT 1

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.

StackExchange Database Administrators Q#193390, answer score: 7