Work with Json field on Postgresql

In PostgreSQL, the json data type can be used to store JSON data. You can use the -> operator to access elements of a JSON object, and the -» operator to access values of a JSON object as text.

For example, consider the following table with a JSON field called data:

CREATE TABLE documents (
    id serial PRIMARY KEY,
    data json
);

To insert a JSON object into the data field, you can use the json_build_object function:

INSERT INTO documents (data)
VALUES (json_build_object('key1', 'value1', 'key2', 'value2'));

To query the values of a JSON object, you can use the -> operator:

SELECT data->'key1' FROM documents;

This will return the value of the key1 field in the JSON object.

You can also use the -» operator to access values as text:

SELECT data->>'key1' FROM documents;

This will return the value of the key1 field as text.

You can also use the jsonb_each function to iterate over the key/value pairs of a JSON object:

SELECT key, value FROM jsonb_each(data) WHERE key='key1';

This will return a row for each key/value pair where the key is ‘key1’.

You can find more information about working with JSON data in PostgreSQL in the PostgreSQL documentation.