Updating a JSON value with SQL
We wanted to be able to update the colours embedded deep within an element, within a page, within the markup column, stored as jsonb in the database. 🥵
TL;DR, here’s the final query:
with t as (
select
c.id creation_id
, ARRAY['pages', page.key, 'elements', element.key, 'altColor'] as path
from
creations c
join lateral jsonb_each(c.markup -> 'pages') page on true
join lateral jsonb_each(page.value) elements on elements.key = 'elements'
join lateral jsonb_each(elements.value) element on element.value @? '$ ? (@.type == "speechbubbles" && @.altColor == "#F7F5F0")'
)
update creations
set markup = jsonb_set(markup, t.path, '"#FFFFFF"')
from t
where id = t.creation_id;Let’s break it down.
We want to get to a place where we can update the markup with an updated block of JSON. We can use jsonb_set to rewrite the JSON, but we need to know the exact path of the field to update:
update creations
set markup = jsonb_set(markup, '{pages,1,elements,1676028704054,altColor}',
'"#FFFFFF"')
where id = ?;The PostgreSQL JSON Functions and Operators page will be a useful reference as you read further.
Building the path
We can use jsonb_setto iterate over JSON objects. So we start by querying the creations table and then use a lateral join to dig down into it.
select page.*
from creations c
join lateral jsonb_each(c.markup -> 'pages') page on trueThis will return a row for each and every page inside the markup column across all creations. In particular, jsonb_each returns a key/valuepair matching the key and value of the JSON object.
| key | value |
|-----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 | {"id": "1", "elements": {"1676028675681": {"z": 2, "id": "1676028675681", "top": 0, "url": "/images/3/source", "left": 0, "type": "image", "width": 1424, "height": 965, "filterUrl": null, "imageType": "photo", "isFlipped": true, "cropFractions": {"top": 0, "left": 0.007873333419046242, "width": 0.9842533331619076, "height": 1}, "backgroundColor": "transparent"}, "1676028704054": {"z": 3, "id": "1676028704054", "top": 185, "left": 126, "type": "speechbubbles", "color": "#2175D9", "width": 868, "height": 317, "altColor": "#F7F5F0", "fontSize": 80, "indentation": 429, "topBubbleChunks": [{"text": "seeing the possible"}], "bottomBubbleChunks": [{"text": "in people"}]}, "1676028800681": {"z": 2, "id": "1676028800681", "top": 1601, "url": "/images/2/source", "left": 160, "type": "image", "width": 307, "height": 271, "filterUrl": null, "imageType": "logo", "isFlipped": false, "cropFractions": {"top": 0.058631921824104226, "left": 0, "width": 1, "height": 0.8827361563517916}, "backgroundColor": null}}} |This is good because we need that page.key(1)value for our path.
Elements
Next, we keep going down the same lines. With another lateral join, we can fetch the elements object from each page. There should only be one for each of our pages (though the elements object may contain multiple elements), so the same number of rows should be returned as before:
elect page.key page_id
, elements.*
from
creations c
join lateral jsonb_each(c.markup -> 'pages') page on true
join lateral jsonb_each(page.value) elements on elements.key = 'elements'| page_id | key | value |
|---------|----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 | elements | {"1676028675681": {"z": 2, "id": "1676028675681", "top": 0, "url": "/images/3/source", "left": 0, "type": "image", "width": 1424, "height": 965, "filterUrl": null, "imageType": "photo", "isFlipped": true, "cropFractions": {"top": 0, "left": 0.007873333419046242, "width": 0.9842533331619076, "height": 1}, "backgroundColor": "transparent"}, "1676028704054": {"z": 3, "id": "1676028704054", "top": 185, "left": 126, "type": "speechbubbles", "color": "#2175D9", "width": 868, "height": 317, "altColor": "#F7F5F0", "fontSize": 80, "indentation": 429, "topBubbleChunks": [{"text": "seeing the possible"}], "bottomBubbleChunks": [{"text": "in people"}]}, "1676028800681": {"z": 2, "id": "1676028800681", "top": 1601, "url": "/images/2/source", "left": 160, "type": "image", "width": 307, "height": 271, "filterUrl": null, "imageType": "logo", "isFlipped": false, "cropFractions": {"top": 0.058631921824104226, "left": 0, "width": 1, "height": 0.8827361563517916}, "backgroundColor": null}} |Filtered elements with element_id
Finally, we can search the individual elements for the ones that we want:
select page.key page_id
, element.key element_id
, element.value speechbubble_element
from
creations c
join lateral jsonb_each(c.markup -> 'pages') page on true
join lateral jsonb_each(page.value) elements on elements.key = 'elements'
join lateral jsonb_each(elements.value) element on element.value @? '$ ? (@.type == "speechbubbles" && @.altColor == "#F7F5F0")'Here we are looking for any elements where type == “speechbubbles” && altColor == “#F7F5F0”. This could return multiple elements for a single page but will also filter out any elements that don’t match the conditions.
| page_id | element_id | speechbubble_element |
|---------|---------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 | 1676028704054 | {"z": 3, "id": "1676028704054", "top": 185, "left": 126, "type": "speechbubbles", "color": "#2175D9", "width": 868, "height": 317, "altColor": "#F7F5F0", "fontSize": 80, "indentation": 429, "topBubbleChunks": [{"text": "seeing the possible"}], "bottomBubbleChunks": [{"text": "in people"}]} |So we will be left with a total number of rows that equals the number of speechbubble elements with the colour we are looking for. Exactly what we want to work with!
Not only that, but as you can see above, we are selecting the page_id and element_id for each of those elements. We finally have all the information we need to update the JSON.
The final JSON update path
We can pull all of this together into an array to create the path selector we need.
select c.id creation_id
, ARRAY['pages', page.key, 'elements', element.key, 'altColor'] as path
from
creations c
join lateral jsonb_each(c.markup -> 'pages') page on true
join lateral jsonb_each(page.value) elements on elements.key = 'elements'
join lateral jsonb_each(elements.value) element on element.value @? '$ ? (@.type == "speechbubbles" && @.altColor == "#F7F5F0")'And there we have it:
| creation_id | path |
|-------------|-------------------------------------------|
| 1 | {pages,1,elements,1676028704054,altColor} |Simply plug this into an update statement, and we are back to the example at the very top of the page.
