Postgres jsonb_set concatenate current value

Question

I'm trying to use jsonb_set to update a range of json objects within my database. I can get a query working that updates the object with a string value, however I cannot seem to get it to update using the current value.

UPDATE entity
SET properties = jsonb_set(properties, '{c_number}', concat('0', properties->>'c_number'))
WHERE type = 1 and length(properties->>'c_number') = 7

The above doesn't work in its current format, I think the issue is the properties->>'c_number' inside the jsonb_set. Is there a way I can access the current value and simply add a leading 0?

nimgwfc · Accepted Answer · 2020-01-21 11:37:19Z

7

Found a solution:

UPDATE entity
SET properties = jsonb_set(properties, '{c_number}', concat('"0', properties->>'c_number', '"')::jsonb)
WHERE type = 1 and length(properties->>'c_number') = 7

answered Jan 21, 2020 at 11:37

nimgwfc

1,5792 gold badges20 silver badges48 bronze badges

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

Comments

mykhailoklym94 · Accepted Answer · 2021-01-12 10:10:34Z

Based on this answer I was able to prepare my solution.

My goal was to create a new property in JSON, with a value that is based on the value of one of the properties which my JSON already has.

For example:

I have:

{
  property_root: { property_root_child: { source_property_key: "source_property_value" } }
}

I want:

{
  property_root: { property_root_child: { source_property_key: "source_property_value", target_property_key: "source_property_value + my custom ending" } }
}

So my query would look:

UPDATE database.table_with_json
SET json_column=jsonb_set(
  json_column, 
  '{ property_root, property_root_child, target_property_key }', 
  concat('"', json_column->'property_root'->'property_root_child'->>'source_property_key', ' + my custom ending', '"')::jsonb) 
WHERE 
  json_column->'property_root'->'property_root_child'->'source_property_key' IS NOT NULL

Why concat looks messy? Based on the answer mentioned above:

The third argument of jsonb_set() should be of jsonb type. The problem is in casting a text string to jsonb string, you need a string in double quotes.

That is why we have to wrap concat in double qoutes.

Collectives™ on Stack Overflow

Postgres jsonb_set concatenate current value

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related