CODEWITHSUNDEEP
jsonpostgresql
TLR
TLR

Reputation: 587

Update json array in postgres

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 ?

Upvotes: 3

Views: 34052

Answers (2)

Ryan Wheale
Ryan Wheale

Reputation: 28410

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[]`

Upvotes: 9

Evan Carroll
Evan Carroll

Reputation: 1

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}$$
);

Upvotes: 4

Related Questions