How to manage JSON data in PostgreSQL

the simple way

Some times we just want to store some non (that) structured data on SQL database and the big issue may be how to manage them.

Having the following planets data:

lets create simple table structure:

CREATE TABLE planets( 
  id SERIAL PRIMARY KEY, 
  name VARCHAR(100),
  num_satellites int, 
  specs JSONB
);

checking:

let’s insert data

INSERT INTO planets 
  (name, num_satellites, specs) 
VALUES 
  ('Earth', 1, '{
                  "alt_name":"gaia", 
                   "main_composition": ["nitrogen", "oxygen"], 
                   "others": {
                      "is_nice": true, 
                      "orbital_period_days": 365
                    }
                 }'
  ), 
  ('Mars', 2, '{
                 "main_composition": [
                    "carbon dioxide", 
                    "argon",
                    "nitrogen", 
                    "oxygen"
                 ],
                 "is_red": true,
                 "others": {
                   "orbital_period_days": 686,
                   "is_nice": "hum..."
                  }
               }'
  ), 
  ('Jupiter', 79, '{"main_composition": ["hydrogen", "helium"]}');

reading the data

SELECT * FROM planets

SELECT jsonb_pretty(specs) FROM planets;

SELECT * FROM planets WHERE specs->>'alt_name' = 'gaia';

let’s go deeper on our filter

SELECT * FROM planets WHERE (specs->>'is_red')::boolean is true;

SELECT * FROM planets WHERE specs->'others'->>'is_nice' =  'hum...';

SELECT name FROM planets WHERE (specs->'main_composition')::jsonb ? 'oxygen';

Updating

this is the current Json on Earth specs

{“alt_name”:”gaia”, “main_composition”: [“nitrogen”, “oxygen”], “others”: {“is_nice”: true, “orbital_period_days”: 365}}

Updating the “alt_name” of Earth to ‘terra’:

UPDATE planets 
SET 
  specs = jsonb_set(specs, '{alt_name}', to_json('terra'::text)::jsonb) 
WHERE Name = 'Earth';

A bit more complex example with the current Json on Mars specs

{“main_composition”: [“carbon dioxide”, “argon”, “nitrogen”, “oxygen”],”is_red”: true,”others”: {“orbital_period_days”: 686,”is_nice”: “hum…”}}

let say Mars is nice, setting “others”:{“is_nice”: true}

UPDATE planets SET specs = jsonb_set(specs, '{others,is_nice}', to_json(true::boolean)::jsonb) WHERE Name = 'Mars';

Jupiter has no ‘exygen’ as “main_composition”, so let’s delete it from our list :D

DELETE FROM planets WHERE NOT (specs->'main_composition')::jsonb ? 'oxygen';

Checking:

SELECT name, jsonb_pretty(specs) AS specs FROM planets;

Conclusion

Storing JSON document forma as a PostgreSQL data is a great option when we have key/value data even if it’s a nested nested document. It’s actually easy to manage. However using psql or other tool to manage data directly on the database can be a difficult task mainly when there is a lot of data to manage or complex JSON structure.

This was just a very small part about JSON. Please check out more infomation on PostgreSQL about JSON Data type and JSON Functions

Next Story, I’ll introduce you some easy way to manage data (including json) using golang.