How to manage JSON data in PostgreSQL

CREATE TABLE planets( 
id SERIAL PRIMARY KEY,
name VARCHAR(100),
num_satellites int,
specs JSONB
);
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"]}');
SELECT * FROM planets
SELECT jsonb_pretty(specs) FROM planets;
SELECT * FROM planets WHERE specs->>'alt_name' = 'gaia';
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';
{“alt_name”:”gaia”, “main_composition”: [“nitrogen”, “oxygen”], “others”: {“is_nice”: true, “orbital_period_days”: 365}}
UPDATE planets 
SET
specs = jsonb_set(specs, '{alt_name}', to_json('terra'::text)::jsonb)
WHERE Name = 'Earth';
{“main_composition”: [“carbon dioxide”, “argon”, “nitrogen”, “oxygen”],”is_red”: true,others: {“orbital_period_days”: 686,”is_nice”: “hum…”}}
UPDATE planets SET specs = jsonb_set(specs, '{others,is_nice}', to_json(true::boolean)::jsonb) WHERE Name = 'Mars';
DELETE FROM planets WHERE NOT (specs->'main_composition')::jsonb ? 'oxygen';
SELECT name, jsonb_pretty(specs) AS specs FROM planets;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store