How to query JSONB, Using Typeorm and postgreSQL

3 min readJul 4, 2021

Storing Json data in postgreSQL gives us many advantage, but querying the table based on the json data is not quiet straightforward and we often face problem while querying the table.

Let’s say we have to query a Token table with a contents JSONB column on a PostgreSQL 9.5+ database.

Here’s how the modal for this table will look like :

import { PrimaryGeneratedColumn, Column, Entity } from 'typeorm';import BaseTable from './BaseTable';
@Entity({ name: 'token' })class Token extends BaseTable {@PrimaryGeneratedColumn()id: number;
@Column()referenceToken: string;
@Column('jsonb', { nullable: false, default: {} })contents: string;}export default Token;

As for BaseTable :

import { Column, BaseEntity, BeforeInsert, Entity, BeforeUpdate } from 'typeorm';abstract class BaseTable extends BaseEntity {@Column()status: number;
@Column()createdAt: number;
@Column()updatedAt: number;}export default BaseTable;

Now that we know what column out table will have we can move to the actual query part.

Lets Start with a simple query. Here we will try to get all the Token for a specific email Id.

const token = (await constructBaseQuery({ entity: Token }).select().where(`Token.contents ::jsonb @> \'{"user":{"email":"${email}"}} \'`,).getMany()) as Token;

This will give us all the token in Token table that contains user object inside contents column data , containing email field that match with email we specified in the query.

But what if we want only record whose status is active.? In that we we can Use AND to add more condition to our search query. Here’s how.

const token = (await constructBaseQuery({ entity: Token }).select().where(`Token.contents ::jsonb @> \'{"user":{"email":"${email}"}} \' AND Token.status=:STATUS_ACTIVE`,{ STATUS_ACTIVE },).getOne()) as Token;

So now we are not just checking the user email but also the status of the token entry.

Select A Token by the name field in contents column

The ->> operator gets a JSON object field as text. Use it if you want to query a simple field in a JSONB column. You might add a B-tree index on contents->>'name'.

Token.contents ->> 'name' = 'shivam'

Now we want to get all the Tokens where name is not null

Token.contents ->> 'name' IS NOT NULL

IN operator on attributes

Token.contents ->> 'name' IN ('sajal', 'shivam');

Finally we want to use LIKE or ILIKE for string matching with some field inside Jsonb contents column

This time lets take a example of nested attribute. so this time we will write a query using @> and ->> both.

const tokens = await constructBaseQuery({ entity: Token }).select().where(`Token.contents ::jsonb @> \'{"user":{"roleId":${roleId}}} \' AND Token.contents ::jsonb -> 'user' ->> 'email' ILIKE '%${searchString}%' AND Token.status=:STATUS_ACTIVE`,{ STATUS_ACTIVE },).orderBy(`${Token.name}.createdAt`, 'DESC').getMany();

This query will get us all the tokens where value of status column is STATUS_ACTIVE and email match the pattern %string% and nested attribute roleId is match with the passed value.

Closing:

Querying on JSONB objects is almost as simple as classic SQL queries, In case of TypeOrm you just need to know the right syntax. I posted only a few examples here, about what we try to use most.

Follow for more such posts.