Working With JSON In PostgreSQL

A collection of useful operations for working with JSON fields I wish to remember. Others would be added here when I encounter them (maybe).

Selecting JSON Fields

Imaging a table with the following schema:

CREATE TABLE data (
  id         INT PRIMARY KEY,
  json_props jsonb
);
INSERT INTO data (id, json_props) VALUES (1, ‘{“foo”:“baz”}');

To select based on the value of foo in the JSON data, use the following query:

SELECT * FROM data WHERE json_props->>'foo' = 'baz';

Sources:

• Stack Overflow

• PostgreSQL documentation for JSON functions and operators.

Modifying JSON Object Properties

Ok, “modifying” is probably not the right word, since SQL is functional. But let say you had a JSON value of the form {“foo”:”bar”} and you wanted to return that object with new or different properties. In PostgreSQL you would use the || operator to do that:

jsonb '{"foo":"bar"}' || jsonb '{"baz":"another"}'
--> {"foo":"bar","baz":"another"}

This comes in handy with updates of existing JSON fields:

UPDATE data SET json_props = json_props || jsonb '{"new":"thing"}'

Source: Stack Overflow (the answer is pretty comprehensive)