I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field.
The node we are trying to move, however, is not fixed; sometimes its index can be 1, 2, or 3.
I currently have this query:
UPDATE table1 t1 SET jsonbfield = jsonb_set(jsonfield ,
'{field1,0,field2,0,configuration}',
(select jsonbfield->'field1'->0->'field2'->0->'field3'->0->'configuration'
from table1 where
jsonbfield->'field1'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME'
and id=t1.id), true)
where jsonbfield->'field'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME';
Which basically would achieve the requirement if the node we were trying to move was always at position 0, however, as mentioned, we noticed that the node we want to move up can be on another positon.
Is there a way to iterate trough each row in JSON, get the position of the node we want to move, save it into a variable and place it in the update statement?
I have tried with a do for, but it seems it doesn't interact well with select jsonb_array_elements
.
Sample Data
{
"parent":[
{
"steps":[
{
"name":"CONFIG1",
"index":1,
"childStep":[
{
"configuration":{
}
}
]
},
{
"name":"CONFIG2",
"index":2,
"childStep":[
{
"configuration":{
"configInfo":{
"info":"someinfo"
}
}
}
]
}
]
}
]
}
This json represents the jsonbfield
row from table1.
What we want to do is move the configuration Node CONFIG2 from childStep to Step Level, so it would look something like this after updating it:
{
"parent":[
{
"steps":[
{
"name":"CONFIG1",
"index":1,
"childStep":[
{
"configuration":{
}
}
]
},
{
"name":"CONFIG2",
"index":2,
"configuration":{
"configInfo":{
"info":"someinfo"
}
}
}
]
}
]
}
The query that i posted, already moves the field up, however, the issue resides on that we can not know if CONFIG2 will be located at index 0, 1, 2, etc.
So we are trying to figure a way to dynamically get where its located and then update it.
With the current query posted here, it assumes CONFIG2 is always located at index 1 (or 0, first position).
Best Answer
db<>fiddle here
I compute the new
jsonb
value in a correlated subquery.WITH ORDINALITY
produces the array index while unnesting the JSON array withjsonb_array_elements()
. The lateridx - 1
adjusts the off-by-1 error from Postgres arrays being 1-based and JSON arrays being 0-based. More aboutWITH ORDINALITY
:I added an outer
WHERE
condition so that only applicable rows are processed to begin with. Avoid updating rows with unchanged values as this adds costs without benefits.You can either use the contains operator
@>
or the new operator@?
in Postgres 12 or later SQL/JSON path language. Either can use an index. See:Update all values for given key nested in JSON array of objects
Index by subkey inside subkey inside array in jsonb