Search code examples
postgresqlnpgsqlcockroachdb

Querying data in postgresql by applying filter on a JSONB column

I have a table with 2 columns; ID (int) and EntityData (JSON). I have created a filter object in form of a Dictionary in C#. I want to fetch all rows which satisfy the key-value pair data in my filter.

A sample filter is:

{
    "Name": "mike",
    "Location": "Lagos"
}

and this should fetch me all rows whose EntityData has Name as mike and Location as Lagos.

Solution

  • A single query could look like the following:

    SELECT * FROM table WHERE json_field->>'Name' = 'mike' AND json_field->>'Location' = 'Lagos'

    You can replace 'mike' and 'Lagos' by parameter placeholders (@name, @location) and rerun this query repeatedly on for all entries of your dictionary. For optimal performance you can also prepare the command beforehand, and possibly even batch all queries in one command by concatenating the query several times into the same CommandText.

    See here for the full PG docs on JSON operators.