Postgres - append to jsonb string

Question

In Postgres, I have a jsonb column foo which stores an array of strings

["a","b","c"]

I need a query which appends another string to whatever is currently there, at a specified index

e.g. Append "!" at index 1

run query: ["a","b","c"] -> ["a","b!","c"]
run again: ["a","b","c"] -> ["a","b!!","c"]
run again: ["a","b","c"] -> ["a","b!!!","c"]

I've implemented this in Postgres v11.2 as follows

UPDATE my_table 
SET foo = jsonb_set(foo, '{1}', CONCAT('"', foo->>1, '!', '"')::jsonb) 
WHERE id = '12345';

_{Note the index 1 and the string '!' are just hardcoded here for simplicity - but they'd be variables.}

It works, but I find it quite inelegant. As you can see, I'm selecting out the string at the given index as text using the ->> operator, using that as input to CONCAT to append the '!', and also to build that back into the correct syntax to convert back to a jsonb string. There is just a lot more work going on here than seems necessary, simply to append to a string at a given path.

Is there a simpler way to do this? A built-in function or operator perhaps, or a simpler way of appending than using CONCAT? (I tried using the || operator in various ways but couldn't seem to make anything work with the syntax & types)

fphilipe · Accepted Answer · 2020-02-22 16:36:12Z

2

I don't think there is a better way than jsonb_set().

The concat can be replaced by || as follows:

jsonb_set(foo, '{1}', ('"' || (foo->>1) || '!"')::jsonb)

answered Feb 22, 2020 at 16:36

fphilipe

10.1k1 gold badge42 silver badges55 bronze badges

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

1 Comment

davnicwil Over a year ago

Thanks - that was the || syntax I was looking for! That's already a big improvement.

Collectives™ on Stack Overflow

Postgres - append to jsonb string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related