Add a Json Value dynamically in POSTGRESQL-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.