Postgres jsonb conditional replace of specific property in array of objects

Question

Imagine I have a column data in a postgres table with the following sample data:

[
  {
    "type": "a",
    "name": "Joe"
  },
  {
    "type": "b",
    "name": "John"
  }
]

I want to perform an update on this table to update the type properties for each object in the json array, converting them from the current text to a corresponding number.
text "a" becomes 1
text "b" becomes 2
and so forth

I got as far as this:

update "table" 
set "data" = jsonb_set("data", '{0,type}','1')

I understand this will update whichever object is at position 0 in the array to have value 1 in the type property, which is of course not what I want. The replace needs to be conditional, if there is an a, it should become a 1, if there is a b, it should become a 2, etc..

Is there any way to accomplish what I'm looking for?

Barbaros Özhan · Accepted Answer · 2021-02-04 18:57:11Z

1

You can use JSONB_SET() function nested in JSONB_AGG() within an UPDATE Statement after producing consecutive integers through use of WITH ORDINALITY keywords following JSONB_ARRAY_ELEMENTS() function such as

UPDATE tab
   SET data = (
               SELECT JSONB_AGG(JSONB_SET(j, '{type}', ('"'||idx||'"')::JSONB))
                 FROM JSONB_ARRAY_ELEMENTS(data)
                 WITH ORDINALITY arr(j,idx)
              )

Demo

answered Feb 4, 2021 at 18:57

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

Thanks for answering, I don't really understand what your suggested query is doing, could you elaborate? I might have given a bad example, I don't want to replace letters of the alphabet by their sequential numbers, I want to do an arbitrary replace of certain strings with certain numbers. (e.g. "type":"chicken" would become "type": 44)

Barbaros Özhan Over a year ago

Hi @Alex , no, not related to the letters of the alphabet, but the sequential index replaces whatever it meets within the array for each extracted individual element, and then aggregates within one element by JSONB_AGG() as been in the original data such as.

Collectives™ on Stack Overflow

Postgres jsonb conditional replace of specific property in array of objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related