JSON

This page considers usage options of JSON format.

Find out more in article JSON functions and operators in official postgres documentation.

Extracting fields

You can extract the value under a particular field of JSON by using the -> '<field>' syntax.

---

The following cell shows extracting a value under a specific JSON key.

--postgreSQL
SELECT ('{"a": 10, "b": 45}'::JSONB) -> 'a' ans; 

SELECT 1

ans
10

The next example demonstrates how this works for columns in a table using the -> operator combination.

--postgreSQL
SELECT
    col->'a' AS a_value,
    col->'b' AS b_value,
    col->'b'->'y' b_y_value
FROM (
    VALUES
    ('{"a": 10, "b": {"x": 10, "y": 20}}'::JSONB),
    ('{"a": 15, "b": {"x": 1, "y": 45}}'::JSONB)
) AS temp(col);

SELECT 2

a_value	b_value	b_y_value
10	{'x': 10, 'y': 20}	20
15	{'x': 1, 'y': 45}	45

Changing value

You can change a json value by applying the jsonb_set function to it. You need to use the syntax jsonb_set(value, '{field1, field2, ...}', <new_value>), which will return JSON where the value under the '{field1, field2, ...}' sequence of keys is changed to <new_value>.

---

The following cell applies jsonb_set to some JSON data that hardcoded to the function. As a result, it returns JSON with the updated value:

--postgreSQL
SELECT jsonb_set('{"A": 80}'::JSONB, '{A}', '10');

SELECT 1

jsonb_set
{'A': 10}

The following example shows how you can modify the value of the inner file. It modifies the value under the “B” key from the sub-JSON that is under the “A” key.

--postgreSQL
SELECT jsonb_set('{"A": {"B": 23}}'::JSONB, '{A, B}', '3')

SELECT 1

jsonb_set
{'A': {'B': 3}}

Condition on field

With syntax <column name> & <value> you can get boolean value that marks if json contains <value> key.

---

The following cell creates tables with different structure jsons and shows the application of the ? operator to them.

--postgreSQL
SELECT
    col,
    col ? 'a' AS contains_a,
    col ? 'b' AS contains_b
FROM (
    VALUES
    ('{"a": 10}'::JSONB),
    ('{"b": 15}'::JSONB)
) AS temp(col);

SELECT 2

col	contains_a	contains_b
{'a': 10}	True	False
{'b': 15}	False	True

If you need to check for keys in nested dictionaries, you can combine ? with the basic substitution operator ->. The following cell checks for the keys foo and bar under the nested key a.

--postgreSQL
SELECT
    col,
    col -> 'a' ? 'foo' AS contains_a_foo,
    col -> 'a' ? 'bar' AS contains_a_bar
FROM (
    VALUES
    ('{"a": {"foo": 10}}'::JSONB),
    ('{"a": {"bar": 16}}'::JSONB)
) AS temp(col);

SELECT 2

col	contains_a_foo	contains_a_bar
{'a': {'foo': 10}}	True	False
{'a': {'bar': 16}}	False	True