Anonymous 1800
I have a question about this fiddle:
<>https://dbfiddle.uk?rdbms=postgres_13&fiddle=6e05b3ae61e453eb7a6896e45627b753
Is it possible to update the value of a specific attributes element by name. For example, for "id"=1 the element with "name"="Case" change the value to "Glass". So it ends up like
{"attributes": [{"name": "Color", "type": "STRING", "value": "Silver"}, {"name": "Case", "type": "STRING", "value": "Glass"}], "ProductName": "XYZ", "ProductType": "ABC"}
Top Answer
PeterVandivier
As a forewarning: if you find yourself needing to do this, it means the data probably wants to be normalised. Answered more fully in...
@@@answer 1855
Fortunately though, the process of transforming this data into the "proper structure" follows many of the same steps as just updating it "in-place". TL;DR:
<>https://dbfiddle.uk/?rdbms=postgres_10&fiddle=d30930f65476de9723c4d8c499dbba0a&hide=1
Note that this does not preserve the original ordinal positioning of the array elements within the `"attributes"` node (although this could likely be done with a bit more verbosity and the use of `row_number()` in the original unwrapping.
Please see linked [answer 1855](databases?q=1618#a1855) for a fuller explanation of the constituent fiddle sections.