Query based on Array of Strings in a JSONB field in PostgreSQL

Sep 13, 2023

Please don’t use ILIKE '%element_of_array%' when querying from JSONB field. Query based on the element of an array of strings in a JSONB field in PostgreSQL using the following steps:

  1. Create a table with a JSONB column. For example:
CREATE TABLE products (
id serial PRIMARY KEY,
name text,
description jsonb
  • Insert data into the table. For example:
INSERT INTO products (name, colors) VALUES
('Product 1', '["red", "blue", "green"]'),
('Product 2', '["cyan", "magenta", "yellow"]');
  • Write a query to select all products where the colors array contains the string red. For example:
SELECT * FROM products
WHERE colors ? 'red';

And that’s how you query from array of strings JSONB field in PostgreSQL.