CODEWITHSUNDEEP
jsonpostgresql
Vinoth Karthick
Vinoth Karthick

Reputation: 1087

Add a Json Value dynamically in POSTGRESQL

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.

Upvotes: 0

Views: 872

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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

Upvotes: 1

Wander Nauta
Wander Nauta

Reputation: 19645

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.

Upvotes: 1

Related Questions