Update json array in postgres

Question

I have a data field that looks like this :

{ "field1" : [{"name":'name1',"value1":true},
              {"name":'name2',"value2":false}
             ],
  "field2" : [{"name":'name1',"value1":true},
              {"name":'name2',"value2":false}
            ]
}

Is it possible to update a specific field with an update ?

create table t_json (
   t_data json
);


insert into t_json values('{"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}]}');

select t_data->'field1' 
from t_json;

I tried this :

update t_json
set t_data->'a' = '[{"value1" : true, "value2" : false}]';

But I get an error : "syntax error at or near ->

What is missing ?

`jsonb_set()`: https://www.postgresql.org/docs/current/static/functions-json.html — , Jan 11 '17 at 14:56
I tried update t_json set t_data = jsonb_set(t_data,'{0,"field1"}','[{"name" : "works"}]'); but it looks like there is no effect ? I'd like to update the whole field — TLR, Jan 11 '17 at 15:23
You need to ditch the `0` as you are not updating the first element of a list, so use only `{field1}` and it should work. Also, edit your question so your datatype is `jsonb` in table creation. — fpietka, Jan 11 '17 at 17:32
Again, you made a typo in your example, as you inserted `field1` twice. You should probably replace the second one. — fpietka, Jan 11 '17 at 17:33

score 8 · Accepted Answer · edited Mar 26 '19 at 00:15

I wanted to post this here in case it helps anybody else. By all means use JSON over JSONB unless you actually need features that JSONB affords you. In general, if you need to perform queries on the JSON data itself, use JSONB. If you are just needing to store data, use JSON.

Anyhow, here is how I am updating a JSON[] field:

UPDATE foo SET bar = ARRAY[$${"hello": "world"}$$, $${"baz": "bing"}$$]::JSON[]

The important things to notice are this:

The array is wrapped like this: ARRAY[ ... ]::JSON[]
Each item in the array is wrapped like this: $${ "foo": "bar" }$$

It is worth noting that this same technique can be used for other array types. For example, if you have a text[] column, the query would look like this:

UPDATE foo SET bar = ARRAY[$$hello world$$, $$baz bing$$]::TEXT[]`

Evan Carroll · Answer 2 · 2017-01-11T22:38:10.823

5

Fixing your typos

Doubt it. This is not valid json. name1 and name2 must be double quoted. To ease working with json, ALWAYS use double quotes. ALWAYS query-quote with double-dollar.

{ "field1" : [{"name":'name1',"value1":true},
              {"name":'name2',"value2":false}
             ],
  "field2" : [{"name":'name1',"value1":true},
              {"name":'name2',"value2":false}
            ]
}

And, what you INSERTED is also funky.. ALWAYS paste beautified valid JSON in your question.

{
  "field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],
  "field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}]
}

Let's change that and fix it.

{
  "field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],
  "field2":[{"name":"name1","value" : true},{"name":"name2","value" : false}]
}

Now let's put it in a query..

TRUNCATE t_json;
INSERT INTO t_json (t_data) VALUES ($$
  {
    "field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],
    "field2":[{"name":"name1","value" : true},{"name":"name2","value" : false}]
  }
$$);

Making the update of the JSON

Now it works.. Now you can update it as you want..

UPDATE t_json
SET t_data = jsonb_set(
  t_data::jsonb,
  '{field1}',
  $${"whatever":1}$$
);

Change from JSON to JSONB

Notice we're having to cast to jsonb. As a general rule, NEVER use JSON (not everyone agrees, see comments). There is no point. Instead use the newer JSONB.

ALTER TABLE t_json ALTER COLUMN t_data TYPE jsonb ;

Now you can do

UPDATE t_json
SET t_data = jsonb_set(
  t_data,
  '{field1}',
  $${"whatever":1}$$
);

edited Jan 11 '17 at 22:38

answered Jan 11 '17 at 22:09

Evan Carroll

78,363
46
261
468

1

"*NEVER use JSON*" - well that depends on what you want to do. JSONB takes up more space. JSON is also a bit faster for inserts and (complete) updates. – Jan 11 '17 at 22:26
1

@a_horse_with_no_name JSONB can be faster for updates too. Depends on if you have to re-validate or not, right? JSONB-JSONB doesn't require re-validation. And, size assumes something of the white space of the original json? It's not hard to imagine the whitespace taking more space than the binary format. In the above example, fully beautified json takes 268bytes. Converting to JSONB shrinks it to 202bytes. That's not to say JSON can't be smaller than the binary representation, but that's an edge case to be concerned with it. It's slower to query. – Evan Carroll Jan 11 '17 at 22:35
As a general rule for a new comer. I think the validated-text types are better off ignored entirely when there is a binary representation. – Evan Carroll Jan 11 '17 at 22:36
1

The JSON in the example takes 170 byte, JSONB 202 byte: http://rextester.com/DOHMP38401 See also here: http://www.glassgraden.com/news/JSON-and-PostgreSQL:-A-Powerful-Combination/ – Jan 11 '17 at 22:53
@Evan Carroll Your query worked but it removed the array from field1 and rather resulted as a simple json object in it and the output was "field1": {"whatever": "1"}, so, what worked in my case was UPDATE t_json SET t_data = jsonb_set( t_data, '{field1}', '[{"whatever":1}]' ); – T Anna Sep 14 '18 at 13:21

Update json array in postgres

2 Answers2

Fixing your typos

Making the update of the JSON

Change from JSON to JSONB

Linked