These days, JSON is widely used for data-interchange mainly because it's light-weight, simple and readable. Many programming languages have a very good support for JSON, either a built-in support or through libraries.
Databases also added support for JSON in recent years. PostgreSQL is no exception in this space. However, each Database adds its own set of JSON functions and specific syntax for working with JSON data stored in its database. There is no standard and one has to become familiar with the database specific syntax and function to save and retrieve JSON data in and out of database. This post mainly focuses on gotchas dealing with JSON in PostgreSQL Database.
PostgreSQL database Version:12.x
Gotcha-1: Updating JSON data
PostgreSQL documentation lists all JSON functions. However, updating JSON data has a Gotcha. It only works if the JSON column has JSON data. If the JSON column is NULL, then the function jsonb_set() doesn't work. It's bit frustrating as it doesn't fail with an error either. It misleads by returning the number of records updated.
Click on the DEMO to check it out.
Given below is the SQL from the above DEMO to try out:
-- check version
SELECT version();
-- create test table
CREATE TABLE test_json (
id varchar(36) NOT NULL,
profile json NULL,
-- age: generated column, value derived from profile json
age smallint GENERATED ALWAYS AS ((profile ->> 'age')::smallint) STORED,
CONSTRAINT pk_giri_test PRIMARY KEY (id)
);
SELECT * FROM test_json;
-- insert a record
INSERT INTO test_json
(id, profile)
VALUES('2eab2d99-167d-41b7-8227-d89ae45d3801', '{"fname":"Giri", "lname":"Pottepalem", "age":30}');
-- check data
SELECT * FROM test_json;
-- insert another record with no profile
INSERT INTO test_json
(id)
VALUES('2eab2d99-167d-41b7-8227-d89ae45d3802');
-- check data
SELECT * FROM test_json;
-- try to update profile using json_set
UPDATE
test_json
SET
profile = jsonb_set(profile::jsonb, '{fname}', '"boo"'::jsonb)
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
-- check data, notice that previous update did not work though it returned 1 rows affected
SELECT * FROM test_json;
-- lets update using regular
UPDATE
test_json
SET
profile = '{"fname":"boo", "lname":"pottepalem"}'::jsonb
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
-- check data, notice that previous update actually updated profile
SELECT * FROM test_json;
-- update one property using jsonb_set
UPDATE
test_json
SET
profile = jsonb_set(profile::jsonb, '{age}', '15'::jsonb)
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
-- check data, notice that jsonb_set now updated profile
SELECT * FROM test_json;
-- update multiple properties using jsonb_set
UPDATE
test_json
SET
profile =
jsonb_set(
jsonb_set(
jsonb_set(
profile::jsonb,
'{age}',
'18'::jsonb
)::jsonb,
'{fname}',
'"Bhuvan"'::jsonb
)::jsonb,
'{lname}',
'"Pottepalem"'::jsonb
)
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
-- check data, notice that jsonb_set now updated profile with multiple properties
SELECT * FROM test_json;
-- update json by concatening with ||, properties that exists get updated, properties that don't exist get added
UPDATE
test_json
SET
profile = profile::jsonb || '{"fname" : "boo", "lname" : "potte", "address" : "dummy address"}'
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
-- check data, notice that existing properties got updated and new properties got added
SELECT * FROM test_json;
-- remove address property from json
UPDATE
test_json
SET
profile = profile::jsonb - 'address'
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
-- check data, notice that address property is removed from the json
SELECT * FROM test_json;
-- update json by concatening with ||, properties that exists get updated, properties that do not exist get added, integer age
UPDATE
test_json
SET
profile = profile::jsonb || '{"fname" : "Bhuvan"}' || '{"lname" : "Pottepalem"}' || '{"age" : 19}' || '{"address" : "dummy address"}'
WHERE
id = '2eab2d99-167d-41b7-8227-d89ae45d3802';
-- check data, notice that existing properties got updated and new properties got added, including generated column age
SELECT * FROM test_json;
-- append another column data to a JSON column
SELECT profile :: jsonb || jsonb_build_object('id', id) as profile_json_with_id_added FROM test_json;
Posts
