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

1

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"
  }
}

edited Jan 10, 2022 at 9:44

answered Jan 10, 2022 at 9:35

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

Comments

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

1

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.

answered Jan 10, 2022 at 9:34

Wander Nauta

19.7k1 gold badge50 silver badges65 bronze badges

Collectives™ on Stack Overflow

Add a Json Value dynamically in POSTGRESQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related