The JSONB type enables you to store and query nested JSON-like data in Postgres. With JSONB, you can to store arbitrarily complex objects and arrays in your Postgres tables, as well as query based on properties in those objects and arrays. You can even use GIN indexes to index nested properties within JSONB objects.

Steps

  • Set up a table with a JSONB column
  • Insert and retrieve JSONB data
  • Query based on JSONB fields
  • Document store using Sequelize ORM
  • Query arrays and objects in JSONB
  • Type casting in JSONB queries
  • Update and modify JSONB data
  • Index JSONB fields using GIN indexes

Set up a table with a JSONB column

To use Postgres as a document store, you can create a table with two columns: an id and a data property that is of type JSONB. You can run the following CREATE TABLE statement in the Neon SQL Editor or from a client such as psql that is connected to Neon.

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  data JSONB
);

JSONB columns can store any JSON object, including objects, arrays, and even nested objects.

Insert and retrieve JSONB data

Run the following SQL to insert two new rows into the documents table. These rows will have data columns with slightly different properties: the first row has a steps property, and the second row has a nested object property, author.

INSERT INTO documents (data)
VALUES (
  '{
    "title": "Neon and JSONB",
    "body": "Using JSONB to store flexible data structures in Postgres.",
    "tags": ["Postgres", "Neon", "JSONB"],
    "steps": ["Set up a table with a JSONB column", "Insert and retrieve JSONB data"]
  }'
),
(
  '{
    "title": "Scaling Neon with Postgres",
    "body": "Learn how to scale your Neon instances with PostgreSQL features.",
    "tags": ["Neon", "Postgres", "scaling"],
    "author": { "name": "John Smith", "age": 30 }
  }'
);

You can then load rows from the documents collection by id. For example, you can load the "Neon and JSONB" row using the following query.

SELECT * FROM documents WHERE id = 1

Query based on JSONB fields

You can also query based on properties in the JSONB column using the ->> operator, which extracts values from the JSONB column. For example, you can load all documents with a given title property using the following query. Note the quotes around title in the WHERE clause.

SELECT * FROM documents WHERE data->>'title' = 'Neon and JSONB'

You can also query based on nested properties. For example, the following query returns all documents whose author property is an object with a name property equal to 'John Smith'.

SELECT * FROM documents WHERE data->'author'->>'name' = 'John Smith'

Document store using Sequelize ORM

Many developers use Postgres through an ORM, like Sequelize in Node.js. ORMs often provide neat syntactic shortcuts for working with JSONB. For example, the following Node.js code shows how you can connect to the existing documents table from previous examples using Sequelize.

import Sequelize from 'sequelize';

const sequelize = new Sequelize(process.env.POSTGRES_CONNECTION_STRING);

const Document = sequelize.define(
  'Document',
  {
    data: {
      type: Sequelize.DataTypes.JSONB,
      allowNull: false,
    },
  },
  { tableName: 'documents', timestamps: false }
);

You can then create rows in the documents collection using the following:

await Document.bulkCreate([
  {
    data: {
      title: 'Neon and JSONB',
      body: 'Using JSONB to store flexible data structures in Postgres.',
      tags: ['Postgres', 'Neon', 'JSONB'],
      author: { name: 'John Smith', age: 30 },
    },
  },
]);

Finally, you can find documents by the author's name using the following query. Note that Sequelize takes care of converting data.author.name to data->'author'->>'name' under the hood.

const documents = await Document.findAll({
  where: {
    'data.author.name': 'John Smith',
  },
});

You can read more about working with JSONB in Sequelize Prisma, and Objection.js on their respective documentation sites.

Query arrays and objects in JSONB

Postgres has several operators that are useful for working with JSONB, including the @> operator, which checks if a given property contains the given value. With arrays, @> can check whether the array contains a given value. For example, the following query returns all documents whose tags property contains the string "JSONB".

SELECT * FROM documents WHERE data->'tags' @> '["JSONB"]'

Note that the right-hand side of @> is a JSON string.

With objects, @> can check whether the document contains one or more properties. For example, the following query returns all documents whose author property has author equal to 'John Smith' and age equal to 30.

SELECT * FROM documents WHERE data->'author' @> '{"name":"John Smith","age":30}'

The query above is equivalent to this query:

SELECT * FROM documents WHERE data->'author'->>'name' = 'John Smith' AND data->'author'->>'age' = '30'

Type casting in JSONB queries

Operators like = and @> are fairly easy to work with: they don't throw any errors if the JSONB property has the wrong type. However, things get a bit more tricky if you want to find all documents whose author's age property is greater than 25. For example, this query throws an "operator does not exist" error:

SELECT *
FROM documents
WHERE (data -> 'author' ->> 'age') > 29;

You need to explicitly cast age to an int type for the above query to run, as shown here:

SELECT *
FROM documents
WHERE (data -> 'author' ->> 'age')::int > 29;

Depending on your data, you may need to add extra checks to avoid throwing an error if a document has an age property that can't be casted to an int. The following query explicitly checks if age is a numeric string before attempting to cast to an int.

SELECT *
FROM documents
WHERE (data -> 'author' ->> 'age') ~ '^\d+$'
  AND (data -> 'author' ->> 'age')::int > 29;

Update and modify JSONB data

You can also update individual properties within your JSONB document without overwriting the entire document using the jsonb_set() function. For example, the following code updates the author.age property to 35 for all documents whose author.name property is "John Smith".

UPDATE documents
SET data = jsonb_set(data, '{author,age}', '35'::jsonb)
WHERE data->'author'->>'name' = 'John Smith';

Note that jsonb_set() expects the nested property name separated by commas (,), not dots (.).

Index JSONB fields using GIN indexes

GIN indexes allow you to index JSONB properties, which can make your queries faster as your data grows. This query shows how you can create a GIN index on the data property:

CREATE INDEX content_idx ON documents USING GIN (data);

To test out the GIN index, let's first insert 100 documents, 1 of which has author.name set to "John Smith", and 99 that do not. Sometimes Postgres decides to skip using indexes and use a sequential scan instead when a query matches most of the table.

DO $$
BEGIN
  FOR i IN 1..100 LOOP
    IF i = 1 THEN
      -- Insert the special document with author name 'John Smith'
      INSERT INTO documents (data)
      VALUES (
        '{
          "title": "Scaling Neon with Postgres",
          "body": "Learn how to scale your Neon instances with PostgreSQL features.",
          "tags": ["Neon", "Postgres", "scaling"],
          "author": { "name": "John Smith", "age": 30 }
        }'::jsonb
      );
    ELSE
      -- Insert general documents for other iterations
      INSERT INTO documents (data)
      VALUES (
        '{
          "title": "Neon and JSONB",
          "body": "Using JSONB to store flexible data structures in Postgres.",
          "tags": ["Postgres", "Neon", "JSONB"],
          "steps": ["Set up a table with a JSONB column", "Insert and retrieve JSONB data"]
        }'::jsonb
      );
    END IF;
  END LOOP;
END $$;

Next, you can run an EXPLAIN ANALYZE query (or just click the "Explain" button in the Neon SQL Editor) to confirm that Postgres is using your GIN index.

EXPLAIN ANALYZE
SELECT *
FROM documents
WHERE data @> '{"author": {"name": "John Smith"}}'::jsonb;

Note that the query above uses the containment operator @>, not WHERE data->'author'->>'name' = 'John Smith'. GIN indexes only support certain operators with JSONB data, including @>.

The EXPLAIN ANALYZE query should produce output that resembles the following. The Bitmap Index Scan means that Postgres is using a GIN index rather than a sequential scan to answer the query.

Bitmap Heap Scan on documents  (cost=8.52..12.54 rows=1 width=245) (actual time=0.014..0.016 rows=3 loops=1)
  Recheck Cond: (data @> '{"author": {"name": "John Smith"}}'::jsonb)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_documents_data  (cost=0.00..8.52 rows=1 width=0) (actual time=0.007..0.007 rows=3 loops=1)
        Index Cond: (data @> '{"author": {"name": "John Smith"}}'::jsonb)
Planning Time: 0.066 ms
Execution Time: 0.096 ms