Work with JSON data

Andrey Aksenov

Contents

Overview
Create a table for JSON data
Load JSON data
Query JSON data
Filter JSON data
JSONB: containment and existence
JSONB indexing
JSON operators and functions

Greengage DB supports the JSON and JSONB types for working with data in JSON format. They are commonly used for storing API responses, application logs, configuration data, and other semi-structured information that does not fit naturally into relational tables.

Greengage DB implements JSON according to the RFC 7159 specification and validates input data against the JSON format rules. Invalid JSON text is rejected during insert or update operations.

Greengage DB also provides a set of JSON functions and operators to extract fields, filter data, transform JSON structures, and build JSON objects and arrays. See JSON operators and functions for details.

Prerequisites

To follow the examples in this topic, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Create a new database and connect to it:

CREATE DATABASE marketplace;
\c marketplace

Greengage DB supports two JSON data types: JSON and JSONB. Both accept the same kinds of values but differ in storage and performance:

JSON stores the exact input text.

It keeps all whitespace, preserves key order, and retains duplicate keys. When duplicate keys are present, JSON processing functions treat the last value as the effective one. Because JSON data must be reparsed each time it is processed, operations are slower.
JSONB stores JSON in a binary format, using a pre-parsed representation for faster operations.

It removes whitespace, ignores key order, and keeps only the last value for duplicate keys. Data input in the JSONB format is slightly slower than in JSON, but subsequent operations on JSONB are significantly faster because the structure is parsed only once. The JSONB type also supports indexing.

In most cases, use JSONB unless you specifically need to preserve formatting or key order.

The RFC 7159 document permits JSON strings to contain Unicode escape sequences denoted by \uXXXX. However, Greengage DB allows only one character set encoding per database. The JSON data type cannot conform strictly to the JSON specification unless the database encoding is UTF-8. Attempts to include characters that cannot be represented in the database encoding will fail. Characters that can be represented in the database encoding but not in UTF-8 are allowed.

The input function for JSON data accepts Unicode escapes regardless of database encoding and checks only that the escape sequence is syntactically correct (\u followed by four hexadecimal digits).
The JSONB input function is stricter:
- Non-ASCII characters (U+0080 and above) are allowed only if the database encoding is UTF-8.
- The null character \u0000 is rejected.
- Unicode surrogate pairs must be valid; valid sequences are converted to the equivalent ASCII or UTF-8 characters for storage.

Many JSON functions automatically convert Unicode escapes to regular characters. If a character cannot be represented in the database encoding, the function will raise an error. To avoid issues, do not mix Unicode escapes with a non-UTF-8 database encoding.

Example:

This command attempts to cast a JSON literal containing the copyright sign to type JSONB:

SELECT '{ "a": "the Copyright \u00A9 sign" }'::jsonb;

In a LATIN1 database, this command fails with the following error:

ERROR:  unsupported Unicode escape sequence

When converting JSON text input into JSONB, the primitive JSON types are mapped to native Greengage DB types, as illustrated in the table below. JSONB imposes a few additional constraints corresponding to the limitations of Greengage DB data types. For example, JSONB rejects numeric values that fall outside the range supported by the Greengage DB NUMERIC type.

JSON primitive type Greengage DB type Notes

JSON primitive type	Greengage DB type	Notes
string	text	`JSONB` does not allow `\u0000` and Unicode escapes of characters that are not supported by the database encoding
number	numeric	The `NaN` and `Infinity` values are not allowed
boolean	boolean	Only lowercase `true` and `false` spellings are accepted
null	(none)	Represents a JSON `null`, which is a typed value within the document and distinct from SQL `NULL` that indicates absence of a value. For example, extracting a JSON `null` as a JSON value (`->`) returns JSON’s `null`, while extracting it as text (`->>`) returns an empty string

string

text

JSONB does not allow \u0000 and Unicode escapes of characters that are not supported by the database encoding

number

numeric

The NaN and Infinity values are not allowed

boolean

Only lowercase true and false spellings are accepted

null

(none)

Represents a JSON null, which is a typed value within the document and distinct from SQL NULL that indicates absence of a value. For example, extracting a JSON null as a JSON value (->) returns JSON’s null, while extracting it as text (->>) returns an empty string

Before you can store JSON documents, create a table with a column of type JSON or JSONB. The example below defines a customer_profiles table that stores each customer profile as a JSONB document:

CREATE TABLE customer_profiles
(
    profile JSONB
)
    WITH (appendoptimized = true)
    DISTRIBUTED RANDOMLY;

To learn more about a table definition, see Tables overview.

To load large volumes of JSON data efficiently, use external tables or the COPY command. For more information on bulk data loading, see:

Append-optimized tables are specifically designed for bulk loading and single-row INSERT statements are not recommended for them.

The following customers.jsonl file contains one JSON object per line, representing individual customer profiles:

{"id": "c001", "name": "Alice Johnson", "email": "alice@example.com", "active": true, "registered": "2020-01-15", "tags": {"membership": ["premium", "newsletter"], "region": "US"}}
{"id": "c002", "name": "Bob Smith", "email": "bob@example.com", "active": false, "registered": "2019-06-30", "tags": {"membership": ["trial"], "region": "EU"}}
{"id": "c003", "name": "Charlie Brown", "email": "charlie@example.com", "active": true, "registered": "2021-09-10"}
{"id": "c004", "name": "Diana Miller", "email": "diana@example.com", "tags": {"region": "UK", "membership": ["newsletter", "trial"]}}

You can load the JSON documents into the table directly from the file using the COPY command:

COPY customer_profiles (profile)
    FROM '/home/gpadmin/examples/json/customers.jsonl';

You can query JSON fields using the JSON operators and functions.

In the example below, top-level fields are extracted from the JSON document using the ->> operator:

SELECT profile ->> 'name'  AS name,
       profile ->> 'email' AS email
FROM customer_profiles;

The result should look like this:

     name      |        email
---------------+---------------------
 Alice Johnson | alice@example.com
 Bob Smith     | bob@example.com
 Charlie Brown | charlie@example.com
 Diana Miller  | diana@example.com
(4 rows)

You can access nested attributes within JSON objects by combining -> and ->> operators: use -> to retrieve a JSON object or array for further navigation, and ->> to extract the final value as plain text:

SELECT profile ->> 'name'             AS name,
       profile -> 'tags' ->> 'region' AS region
FROM customer_profiles;

The result looks as follows:

     name      | region
---------------+--------
 Alice Johnson | US
 Bob Smith     | EU
 Charlie Brown |
 Diana Miller  | UK
(4 rows)

Alternatively, use the jsonb_extract_path_text() function to extract nested values by specifying the path explicitly:

SELECT profile ->> 'name'                                 AS name,
       jsonb_extract_path_text(profile, 'tags', 'region') AS region_json
FROM customer_profiles;

Learn more about retrieving rows from tables and views in the SELECT command overview topic.

You can filter rows based on JSON field values using the WHERE or HAVING clauses. JSON operators can be used to compare fields directly, including nested attributes.

This example filters records where a top-level JSON key matches a specific value:

SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile ->> 'active' = 'true';

The result is:

     name
---------------
 Alice Johnson
 Charlie Brown
(2 rows)

In this example, filtering is performed by a nested JSON key:

SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile -> 'tags' ->> 'region' = 'US';

The output should look like this:

     name
---------------
 Alice Johnson
(1 row)

You can also cast JSON text values to native types for comparison:

SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE (profile ->> 'registered')::date > '2020-01-01';

The output looks as follows:

     name
---------------
 Alice Johnson
 Charlie Brown
(2 rows)

The JSONB type supports containment and existence operators:

Containment checks whether one JSON document is contained within another
Existence tests whether a specified key or array element appears at the top level of a JSON value.

The example below uses the @> operator to return rows where the JSONB column contains a specified subdocument:

SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile -> 'tags' @> '{"region": "EU"}';

The result is:

   name
-----------
 Bob Smith
(1 row)

This query returns rows where the top-level JSON object contains the specified key:

SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile ? 'tags';

The result looks as follows:

     name
---------------
 Alice Johnson
 Bob Smith
 Diana Miller
(3 rows)

This query returns rows where the JSON object contains any of the specified keys.

SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile ?| ARRAY ['active', 'registered'];

The output looks like this:

     name
---------------
 Charlie Brown
 Alice Johnson
 Bob Smith
(3 rows)

JSONB supports GIN and B-tree indexes:

GIN indexes efficiently search for keys or key/value pairs across many JSONB documents.
B-tree indexes are useful mainly for comparing or checking the equality of entire JSON documents.

GIN indexes support two operator classes for JSONB:

jsonb_ops — the default GIN operator class that supports queries with the @>, ?, ?&, and ?| operators. This class provides more flexibility but can take up more index space and has slightly slower performance for containment queries.
jsonb_path_ops — the non-default GIN operator class that supports indexing the @> operator only. It indexes only the paths and values needed for containment checks, resulting in a smaller index size and faster lookups.

Create a GIN index on the profile column of the customer_profiles table:

CREATE INDEX profile_gin_idx ON customer_profiles USING gin (profile);

After creating the index, run ANALYZE to update table statistics for the query planner:

ANALYZE;

Check that the index is used to find JSONB documents matching a containment condition:

EXPLAIN (COSTS OFF)
SELECT profile ->> 'name' AS name
FROM customer_profiles
WHERE profile @> '{"active": false}';

The result should include Bitmap Index Scan on the profile_gin_idx index:

                               QUERY PLAN
-------------------------------------------------------------------------
 Gather Motion 4:1  (slice1; segments: 4)
   ->  Result
         ->  Bitmap Heap Scan on customer_profiles
               Recheck Cond: (profile @> '{"active": false}'::jsonb)
               ->  Bitmap Index Scan on profile_gin_idx
                     Index Cond: (profile @> '{"active": false}'::jsonb)
 Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)

The following operators are available for JSON and JSONB data types.

Operator: ->

Right operand type: int

Gets the JSON array element at the specified zero-based index.

Example:

SELECT '[
  {
    "id": 1,
    "name": "Alice"
  },
  {
    "id": 2,
    "name": "Bob"
  }
]'::jsonb -> 1;

Result:

         ?column?
--------------------------
 {"id": 2, "name": "Bob"}
(1 row)

Operator: ->

Right operand type: text

Gets the JSON object field by key.

Example:

SELECT '{
  "user": {
    "name": "Alice",
    "email": "alice@example.com"
  }
}'::jsonb -> 'user';

Result:

                    ?column?
-------------------------------------------------
 {"name": "Alice", "email": "alice@example.com"}
(1 row)

Get JSON array element as text (->>)

Operator: ->>

Right operand type: int

Gets the JSON array element as text.

Example:

SELECT '[
  "apple",
  "banana",
  "cherry"
]'::jsonb ->> 1;

Result:

 ?column?
----------
 banana
(1 row)

Get JSON object field as text (->>)

Operator: ->>

Right operand type: text

Gets the JSON object field as text.

Example:

SELECT '{
  "product": "Book",
  "price": 9.99
}'::jsonb ->> 'product';

Result:

 ?column?
----------
 Book
(1 row)

Operator: #>

Right operand type: text[]

Gets the JSON object at the specified path.

Example:

SELECT '{
  "customer": {
    "address": {
      "city": "Amsterdam",
      "zip": "1011AB"
    }
  }
}'::jsonb #> '{customer,address}';

Result:

                ?column?
----------------------------------------
 {"zip": "1011AB", "city": "Amsterdam"}
(1 row)

Get JSON object at path as text (#>>)

Operator: #>>

Right operand type: text[]

Gets the JSON object at the specified path as text.

Example:

SELECT '{
  "customer": {
    "address": {
      "city": "Amsterdam",
      "zip": "1011AB"
    }
  }
}'::jsonb #>> '{customer,address,city}';

Result:

 ?column?
-----------
 Amsterdam
(1 row)

The following operators are available for JSONB values only. Many of these operators can be indexed by JSONB operator classes.

Operator: @>

Right operand type: JSONB

Checks whether the left JSON value contains the right JSON value.

Example:

SELECT '{
  "name": "Alice",
  "active": true,
  "role": "admin"
}'::jsonb @> '{
  "role": "admin"
}'::jsonb;

Result:

 ?column?
----------
 t
(1 row)

Operator: <@

Right operand type: JSONB

Checks whether the left JSON value is contained within the right JSON value.

Example:

SELECT '{
  "role": "admin"
}'::jsonb <@ '{
  "name": "Alice",
  "active": true,
  "role": "admin"
}'::jsonb;

Result:

 ?column?
----------
 t
(1 row)

Operator: ?

Right operand type: text

Checks whether the key or element string exists in the JSON value.

Example:

SELECT '{
  "name": "Alice",
  "email": "alice@example.com"
}'::jsonb ? 'address';

Result:

 ?column?
----------
 f
(1 row)

Operator: ?|

Right operand type: text[]

Checks whether any of the specified keys or elements exist in the JSON value.

Example:

SELECT '{
  "name": "Alice",
  "email": "alice@example.com",
  "active": true
}'::jsonb ?| array ['phone','email'];

Result:

 ?column?
----------
 t
(1 row)

Operator: ?&

Right operand type: text[]

Checks whether all the specified keys or elements exist.

Example:

SELECT '{
  "name": "Alice",
  "email": "alice@example.com",
  "active": true
}'::jsonb ?& array ['phone','email'];

Result:

 ?column?
----------
 f
(1 row)

This section covers functions that generate JSON values. Although these functions do not have separate JSONB versions, you can cast their results to JSONB.

Function: array_to_json(anyarray [, pretty_bool])

Returns the array as a JSON array. A multidimensional array becomes a JSON array of arrays. Line feeds are added between top-level elements if pretty_bool is true.

Example:

SELECT array_to_json('{{10, 20}, {30, 40}}'::int[]);

Result:

   array_to_json
-------------------
 [[10,20],[30,40]]
(1 row)

Function: json_build_array(VARIADIC "any")

Builds a JSON array with elements of potentially different types from a VARIADIC argument list.

Example:

SELECT json_build_array(1001, 'Alice', true, 89.5);

Result:

      json_build_array
-----------------------------
 [1001, "Alice", true, 89.5]
(1 row)

Function: json_build_object(VARIADIC "any")

Builds a JSON object out of a VARIADIC argument list. The argument list is taken in order and converted to a set of key/value pairs.

Example:

SELECT json_build_object('id', 1001, 'name', 'Alice', 'active', true);

Result:

                json_build_object
--------------------------------------------------
 {"id" : 1001, "name" : "Alice", "active" : true}
(1 row)

Function: json_object(text[])

Builds a JSON object out of a text array. The array must be either a one- or two-dimensional array. The one-dimensional array must have an even number of elements. The elements are taken as key/value pairs.

For a two-dimensional array, each inner array must have exactly two elements, which are taken as a key/value pair.

Example 1:

SELECT json_object('{id, 1, name, "Book", price, "19.99"}');

Result:

                   json_object
--------------------------------------------------
 {"id" : "1", "name" : "Book", "price" : "19.99"}
(1 row)

Example 2:

SELECT json_object('{{id, 1}, {name, "Book"}, {price, "19.99"}}');

Result:

                   json_object
--------------------------------------------------
 {"id" : "1", "name" : "Book", "price" : "19.99"}
(1 row)

Function: json_object(keys text[], values text[])

Builds a JSON object out of a text array. This form of json_object() takes keys and values pairwise from two separate arrays. Otherwise, it behaves the same as the one-argument form.

Example:

SELECT json_object('{id, name}', '{1, "Book"}');

Result:

          json_object
-------------------------------
 {"id" : "1", "name" : "Book"}
(1 row)

Function: row_to_json(record [, pretty_bool])

Returns the row as a JSON object. Line feeds are added between top-level elements if pretty_bool is true.

Example:

SELECT row_to_json(row (10, 'Laptop'));

Result:

       row_to_json
-------------------------
 {"f1":10,"f2":"Laptop"}
(1 row)

Function: to_json(anyelement)

Returns the value as a JSON object. Arrays and composites are processed recursively and are converted to arrays and objects. If the input contains a cast from the type to JSON, the cast function is used to perform the conversion; otherwise, a JSON scalar value is produced. For any scalar type other than a number, a boolean, or a null value, the text representation will be used, properly quoted and escaped so that it is a valid JSON string.

Example:

SELECT to_json('Alice said "Hi!"'::text);

Result:

       to_json
----------------------
 "Alice said \"Hi!\""
(1 row)

Sample table

CREATE TABLE products
(
    id    SERIAL,
    name  VARCHAR(100),
    price DECIMAL(10, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

INSERT INTO products (name, price)
VALUES ('Laptop', 999.99),
       ('Smartphone', 499.99),
       ('Headphones', 89.99);

Function: json_agg(<record>)

Aggregates records as a JSON array of objects.

Example:

SELECT json_agg(name ORDER BY price DESC) AS product_names
FROM products;

Result:

             product_names
----------------------------------------
 ["Laptop", "Smartphone", "Headphones"]
(1 row)

Function: json_object_agg(name, value)

Aggregates name/value pairs as a JSON object.

Example:

SELECT json_object_agg(id, name ORDER BY price DESC) AS products_map
FROM products;

Result:

                        products_map
------------------------------------------------------------
 { "1" : "Laptop", "2" : "Smartphone", "3" : "Headphones" }
(1 row)

This section lists the functions available for processing JSON and JSONB values.

Function: json_array_elements(json), jsonb_array_elements(jsonb)

Return type: setof json, setof jsonb

Expands a JSON array to a set of JSON values.

Example:

SELECT *
FROM jsonb_array_elements('[
  {
    "name": "Alice"
  },
  {
    "name": "Bob"
  },
  {
    "name": "Carol"
  }
]');

Result:

       value
-------------------
 {"name": "Alice"}
 {"name": "Bob"}
 {"name": "Carol"}
(3 rows)

json_array_elements_text() / jsonb_array_elements_text()

Function: json_array_elements_text(json), jsonb_array_elements_text(jsonb)

Return type: setof text

Expands a JSON array to a set of text values.

Example:

SELECT *
FROM jsonb_array_elements_text('["Laptop","Smartphone","Headphones"]');

Result:

   value
------------
 Laptop
 Smartphone
 Headphones
(3 rows)

Function: json_array_length(json), jsonb_array_length(jsonb)

Return type: int

Returns the number of elements in the outermost JSON array.

Example:

SELECT json_array_length('[{"name":"Alice"},{"name":"Bob"},{"name":"Carol"}]');

Result:

 json_array_length
-------------------
                 3
(1 row)

Function: json_each(json), jsonb_each(jsonb)

Return type: setof record with columns:

key text
value json (json_each) / value jsonb (jsonb_each)

Expands the outermost JSON object into a set of key/value pairs.

Example:

SELECT *
FROM json_each('{
  "product": "Laptop",
  "price": 999.99,
  "in_stock": true
}');

Result:

   key    |  value
----------+----------
 product  | "Laptop"
 price    | 999.99
 in_stock | true
(3 rows)

json_each_text() / jsonb_each_text()

Function: json_each_text(json), jsonb_each_text(jsonb)

Return type: setof record with columns:

key text
value text

Expands the outermost JSON object into a set of key/value pairs. All values are returned as text.

Example:

SELECT *
FROM json_each_text('{
  "product": "Laptop",
  "price": 999.99,
  "in_stock": true
}');

Result:

   key    | value
----------+--------
 product  | Laptop
 price    | 999.99
 in_stock | true
(3 rows)

Function: json_extract_path(from_json json, VARIADIC path_elems text[]), jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])

Return type: JSON, JSONB

Returns the JSON value pointed to by path_elems. Equivalent to the #> operator (see Get JSON object at path (#>)).

Example:

SELECT jsonb_extract_path(
               '{
                 "order": {
                   "id": 123,
                   "customer": {
                     "name": "Alice",
                     "vip": true
                   }
                 }
               }',
               'order', 'customer'
       );

Result:

       jsonb_extract_path
--------------------------------
 {"vip": true, "name": "Alice"}
(1 row)

json_extract_path_text() / jsonb_extract_path_text()

Function: json_extract_path_text(from_json json, VARIADIC path_elems text[]), jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])

Return type: text

Returns the JSON value pointed to by path_elems as text. Equivalent to the #>> operator (see Get JSON object at path as text (#>>)).

Example:

SELECT jsonb_extract_path_text(
               '{
                 "order": {
                   "id": 123,
                   "customer": {
                     "name": "Alice",
                     "vip": true
                   }
                 }
               }',
               'order', 'customer', 'name'
       );

Result:

 jsonb_extract_path_text
-------------------------
 Alice
(1 row)

Function: json_object_keys(json), jsonb_object_keys(jsonb)

Return type: setof text

Returns a set of keys in the outermost JSON object.

Example:

SELECT *
FROM json_object_keys('{
  "id": 1,
  "name": "Laptop",
  "price": 999.99
}');

Result:

 json_object_keys
------------------
 id
 name
 price
(3 rows)

Function: json_populate_record(base anyelement, from_json json), jsonb_populate_record(base anyelement, from_json jsonb)

Return type: anyelement

Expands the object in from_json to a row whose columns match the record type defined by base.

Example:

CREATE TYPE product_info AS
(
    id    INT,
    name  TEXT,
    price NUMERIC
);

SELECT *
FROM json_populate_record(NULL::product_info,
                          '{
                            "id": 1,
                            "name": "Laptop",
                            "price": 999.99
                          }');

Result:

 id |  name  | price
----+--------+--------
  1 | Laptop | 999.99
(1 row)

Function: json_populate_recordset(base anyelement, from_json json), jsonb_populate_recordset(base anyelement, from_json jsonb)

Return type: setof anyelement

Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base.

Example:

CREATE TYPE item_info AS
(
    id  INT,
    qty INT
);

SELECT *
FROM json_populate_recordset(NULL::item_info,
                             '[
                               {
                                 "id": 1,
                                 "qty": 10
                               },
                               {
                                 "id": 2,
                                 "qty": 25
                               },
                               {
                                 "id": 3,
                                 "qty": 40
                               }
                             ]');

Result:

 id | qty
----+-----
  1 |  10
  2 |  25
  3 |  40
(3 rows)

Function: json_to_record(json), jsonb_to_record(jsonb)

Return type: record

Builds an arbitrary record from a JSON object. As with all functions returning a record, the caller must explicitly define the structure of the record with an AS clause.

Example:

SELECT *
FROM json_to_record('{"id":1,"name":"Laptop","price":999.99}')
         AS x(id INT, name TEXT, price NUMERIC);

Result:

 id |  name  | price
----+--------+--------
  1 | Laptop | 999.99
(1 row)

Function: json_to_recordset(json), jsonb_to_recordset(jsonb)

Return type: setof record

Builds an arbitrary set of records from a JSON array of objects. As with all functions returning a record, the caller must explicitly define the structure of the record with an AS clause.

Example:

SELECT *
FROM json_to_recordset('[{"id":1,"name":"Laptop"},{"id":2,"name":"Smartphone"}]')
         AS x(id INT, name TEXT);

Result:

 id |    name
----+------------
  1 | Laptop
  2 | Smartphone
(2 rows)

Function: json_typeof(json), jsonb_typeof(jsonb)

Return type: text

Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

The json_typeof() function’s return value of null should not be confused with an SQL NULL. Calling json_typeof('null'::json) returns the text value null, while calling json_typeof(NULL::json) returns an SQL NULL.

Example:

SELECT json_typeof('{"name":"Alice"}'),
       json_typeof('["a","b","c"]'),
       json_typeof('123'),
       json_typeof('true');

Result:

 json_typeof | json_typeof | json_typeof | json_typeof
-------------+-------------+-------------+-------------
 object      | array       | number      | boolean
(1 row)

NOTE

The examples of json_populate_record(), json_populate_recordset(), json_to_record(), and json_to_recordset() use constant values. In practice, these functions are usually applied to JSON or JSONB columns from a table in the FROM clause. The extracted key values can then be used in other parts of the query, such as the WHERE clause or the target list. Extracting multiple values at once this way is often faster than using separate operators for each key.

Window functions

Analyze queries

Work with JSON data

Overview

About JSON data

JSON and JSONB encoding

JSONB conversion

Create a table for JSON data

Load JSON data

Query JSON data

Filter JSON data

JSONB: containment and existence

JSONB indexing

JSON operators and functions

JSON operators

Get JSON array element (->)

Get JSON object field (->)

Get JSON array element as text (->>)

Get JSON object field as text (->>)

Get JSON object at path (#>)

Get JSON object at path as text (#>>)

JSONB operators

Contains (@>)

Contained within (<@)

Key/element exists (?)

Any key/element exists (?|)

All keys/elements exist (?&)

JSON creation functions

array_to_json()

json_build_array()

json_build_object()

json_object(text[])

json_object(text[], text[])

row_to_json()

to_json()

JSON aggregate functions

json_agg()

json_object_agg()

JSON processing functions

json_array_elements() / jsonb_array_elements()

json_array_elements_text() / jsonb_array_elements_text()

json_array_length() / jsonb_array_length()

json_each() / jsonb_each()

json_each_text() / jsonb_each_text()

json_extract_path() / jsonb_extract_path()

json_extract_path_text() / jsonb_extract_path_text()

json_object_keys() / jsonb_object_keys()

json_populate_record() / jsonb_populate_record()

json_populate_recordset() / jsonb_populate_recordset()

json_to_record() / jsonb_to_record()

json_to_recordset() / jsonb_to_recordset()

json_typeof() / jsonb_typeof()