Add a Json Value dynamically in POSTGRESQL

Question

In postgresql, I've a table with a column named source of type json.

The source contains Json as below. I need to select the source column but the output should produce the result as below

Input

{ "filters": { "operator": "EQ", "value": "name", "type": "string" } }

Output

 { "filters": { "operator": "EQ", "value": "name", "type": "string", "args": ["cat"] } }

I was able to achieve this programatically using javascript, but please help me if it can be done via postgresql select statement itself.

What should happen if the input already contains an args property? — Bergi
– Bergi, Commented Jan 10, 2022 at 9:41
If args contain, it should replace it. In my case, it won't be present — Vinoth Karthick
– Vinoth Karthick, Commented Jan 10, 2022 at 10:21

Stefanov.sm · Accepted Answer · 2022-01-10 09:44:16Z

Use jsonb_set function.

select jsonb_set( '{ "filters": { "operator": "EQ", "value": "name", "type": "string" } }'::jsonb, -- the input value '{filters,args}', -- new element path '["cat"]' -- new element value );

Result:

{ "filters": { "args": ["cat"], "type": "string", "value": "name", "operator": "EQ" } }

Wander Nauta · Accepted Answer · 2022-01-10 09:34:44Z

The jsonb_set or jsonb_insert functions can be used for this. For example:

SELECT jsonb_set(myvalue, '{filters,args}', '["cat"]') FROM mytable;

will select the myvalue column from mytable, except adding the "args" value as in your example.

The JSON Functions and Operators and JSON Types sections of the documentation have more detailed information.

Collectives™ on Stack Overflow

Add a Json Value dynamically in POSTGRESQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related