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.
Best Answer
Use the index of the element number as instructed in the manual. For longer paths, the path notation is shorter:
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 withjsonb_array_elements()
in aLATERAL
join and filter the one you want.Returns nothing if no matching element is found.
WITH ORDNALITY
andelem_blue2
are not needed here, but demonstrate a technique we are going to use in the next step.More explanation:
Since Postgres 9.5, there is
jsonb_set()
. To update the value of theqty
key of the first element: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: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 theFROM
clause another time to allow theLATERAL
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 innerSELECT
and the outerUPDATE
. 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
orMATERIALIZED VIEW
. The redesign is beyond the scope of this question.