I create a table with a Json type column. I need to update one key:value
in Json. i.e increment value of 3 to 5. Can i do it?
forough m wrote:
I create a table with a Json type column. I need to update one key:value
in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like this:
CREATE TABLE jsontest (
id integer PRIMARY KEY,
j jsonb
);
INSERT INTO jsontest VALUES (1, '{"a": 12, "b": {"c": 12, "d": 4}}');
TABLE jsontest;
id | j
----+-----------------------------------
1 | {"a": 12, "b": {"c": 12, "d": 4}}
(1 row)
UPDATE jsontest
SET j = jsonb_set(
j,
'{b,c}',
((j #>> '{b,c}')::integer + 30)::text::jsonb
)
WHERE id = 1;
TABLE jsontest;
id | j
----+-----------------------------------
1 | {"a": 12, "b": {"c": 42, "d": 4}}
(1 row)
forough m wrote:
I create a table with a Json type column. I need to update one key:value
in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like this:
CREATE TABLE jsontest (
id integer PRIMARY KEY,
j jsonb
);
INSERT INTO jsontest VALUES (1, '{"a": 12, "b": {"c": 12, "d": 4}}');
TABLE jsontest;
id | j
----+-----------------------------------
1 | {"a": 12, "b": {"c": 12, "d": 4}}
(1 row)
UPDATE jsontest
SET j = jsonb_set(
j,
'{b,c}',
((j #>> '{b,c}')::integer + 30)::text::jsonb
)
WHERE id = 1;
TABLE jsontest;
id | j
----+-----------------------------------
1 | {"a": 12, "b": {"c": 42, "d": 4}}
(1 row)
Sorry. I test it but it has no result. SQL query executed without anyI create a table with a Json type column. I need to update one
key:value in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like this:
[...]
error but value in Json not changed.
forough m wrote:
Sorry. I test it but it has no result. SQL query executed without anyI create a table with a Json type column. I need to update one
key:value in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like this:
[...]
error but value in Json not changed.
Then you must be doing something wrong.
Hard to say what if you don't show your query.
On Thursday, March 1, 2018 at 1:48:05 PM UTC+3:30, Laurenz Albe wrote:
forough m wrote:
Sorry. I test it but it has no result. SQL query executed without any error but value in Json not changed.I create a table with a Json type column. I need to update one
key:value in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like this: >>
[...]
Then you must be doing something wrong.
Hard to say what if you don't show your query.
Worked. Thanks :) But, why it remove all fields if {b,c} not exist?
I want to insert it if not exist
I create a table with a Json type column. I need to update one
key:value in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like
this:
Worked. Thanks :) But, why it remove all fields if {b,c} not exist?
I want to insert it if not exist
I want to say: If 'f' exist, then increment it +3 else, if not exist
create it with value = 0
forough m wrote:
I create a table with a Json type column. I need to update one
key:value in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like
this:
Worked. Thanks :) But, why it remove all fields if {b,c} not exist?
I want to insert it if not exist
I want to say: If 'f' exist, then increment it +3 else, if not exist
create it with value = 0
You could use CASE:
UPDATE t SET j = CASE WHEN <j contains key>
THEN <j with 3 added>
ELSE <j with new key>
END
forough m wrote:
I create a table with a Json type column. I need to update one
key:value in Json. i.e increment value of 3 to 5. Can i do it?
If you have PostgreSQL 9.5 or better, you can use "json_set" like
this:
Worked. Thanks :) But, why it remove all fields if {b,c} not exist?
I want to insert it if not exist
I want to say: If 'f' exist, then increment it +3 else, if not exist
create it with value = 0
You could use CASE:
UPDATE t SET j = CASE WHEN <j contains key>
THEN <j with 3 added>
ELSE <j with new key>
END
You could use CASE:
UPDATE t SET j = CASE WHEN <j contains key>
THEN <j with 3 added>
ELSE <j with new key>
END
Wow, very good. thank you. How should i check <j contains key> ?
is right there.
On Wed, 07 Mar 2018 00:09:05 -0800, forough m wrote:
You could use CASE:
UPDATE t SET j = CASE WHEN <j contains key>
THEN <j with 3 added>
ELSE <j with new key>
END
Wow, very good. thank you. How should i check <j contains key> ?
Hm. Why don't you look at the documentation, section
"JSON functions and operators":
https://www.postgresql.org/docs/current/static/functions- json.html#FUNCTIONS-JSONB-OP-TABLE
is right there.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (3 / 13) |
Uptime: | 91:32:54 |
Calls: | 6,497 |
Calls today: | 8 |
Files: | 12,100 |
Messages: | 5,277,697 |