Search for specific string in JSONB array ?

I have a column called meta_data whose data type is JSONB data type which deals with JSON array. And let’s say you wanted to find rows that contains the string “deviceid” in the meta_data column (JSONB data type) in audit trail database table. Let’s learn how to write query to search for specific string in JSONB array.

Search for specific string in JSONB array

If you are using PostgreSQL 11 or older you can use the following query to search for a text in database column of JSONB data type.

select * from audittrail
where meta_data::text like '%deviceid%';

--(OR)

select * from audittrail
where meta_data::text like '%"deviceid"%';
-- if you are looking for the exact value

In case if you are using PostgreSQL 12 and above, then you can use the following query to search for a string in JSONB or JSON array.

select * from audittrail
where jsonb_path_exists(meta_data, '$.** ? (@.type() == "string" && @ like_regex "deviceid")')

Query Explanation:

$.** — find any value at any level
? — where
@.type() == “string” — value is string
&& — and
@ like_regex “deviceid” — value contains ‘deviceid’

What is JSONB data type ?

PostgreSQL added support for jsonb data type in version 9.4. Note, that the letter ‘b’ at the end stands for ‘better’.

Note, there are two JSON data types in PostgreSQL 9.4: json and jsonb. They almost accept similar values as input, but the major difference is efficiency.

The json data type stores the exact copy of the text provided as input and its processing functions requires to reparse the data on each execution. But, the jsonb data is stored in special binary format and this format is compressed and more efficient than the text format. And regarding data processing for jsonb it is significantly faster compared to json as it does not require reparsing. Also, jsonb supports indexing and this can be significant advantage.

To know more about the JSON and JSONB functions and operators please refer this JSON functions and operators.

Hope this sneppet on how to search for specific string in JSONB is helpful 🙂

How to search a specific string in JSON or JSONB array in PostgreSQL ?