How to manage JSON data in PostgreSQL

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

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 =…