Update nth element of array using a WHERE clause

Question

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"}]'

2. 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.

Answer 1

1. 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.

• Arash: i was expecting you with your great answers.you answer with care.a:(i stored integer as strings by mistake,my bad). 1.in update query whats the best way to get current value of 'qty' and subtract desired amount from it on the fly? 2.i take your advise about changing design.but i cant figure it out.i think its okay to keep my products info in this document and just change parts require frequent updates.so what you suggest for options part?is it good idea to make 'qty' value point to a FK of another table that keeps all of item quantities?
• Erwin Brandstetter: @ArashMoosapour: 1. I built that and some more into query. 2. Yes, separate 1:n table with scalar column. The redesign depends on the complete situation, I added a bit above.
• Christophe Roussy: @ErwinBrandstetter all your answers are superb, will you write a book about advanced PostgreSQL some day ?