Postgres 14 JSON Cheat Sheet

postgresjsoncheatsheet.md

Postgres JSON Cheat Cheat Sheet

Select

SELECT data['title'] as title FROM blogs WHERE data['author']['first_name'] = '"Ada"';

Insert

INSERT INTO blogs (data) VALUES ('{"title": "blog one", "author": {"first_name": "Ada", "last_name": "Love"}}' );

INSERT INTO blogs (data) VALUES 
 ('{"title": "blog one", "author": {"first_name": "Ada", "last_name": "Love"}}' ),
 ('{"title": "blog two", "author": {"first_name": "Star", "last_name": "Work"}}' );

Update

UPDATE blogs SET data['field'] =' "javascript" ' WHERE id = 1;

UPDATE blogs SET data['field']['subfield'] =' "javascript two" ' WHERE id = 1;

UPDATE blogs SET data['otherdata'][3]['address'] = ' "New York" ' WHERE id = 2;

UPDATE blogs SET data['tags'][4] =' "javascript" ' WHERE id = 1;

Delete

DELETE FROM blogs WHERE data['author']['first_name'] = '"Ada"';