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;

System Architect

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Day14 of #100DaysOfCode

Reducing the boilerplate code in NGRX

Atom for React & JavaScript Developers — 2020

Atom

Skills Recommended for a JavaScript Enthusiast

ReciPal: A First Chrome Extension

Why React Native & React JS are popular? — WalkingTree Technologies

React Native: Creating an In-App Inbox

React Native Features and Advantages

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
Jorge Quitério

Jorge Quitério

System Architect

More from Medium

Working with Pulumi Objects and Python Serialization

ALL ABOUT EMBEDDED AND DYNAMIC SQL

Somewhat complex reporting and data analysis in MySql 8 — Part 1

Starting SQL: FULL OUTER JOINs