Postgresql – Postgres: querying jsonb array with an array

jsonpostgresqlpostgresql-9.4rails

My products table has a jsonb column called 'specs'.

This specs hash will often have an array field, let's call it 'spec_options'.

When querying this table, I need to find any product that has certain spec options.

Let's say we have a product with these spec_options:

['b', 'c', 'd']

And the user wants to find products with these spec_options:

['a', 'b', 'c']

The query I need should return find above product.

This where clause worked just fine when the field wasn't an array:

'["a", "b", "c"]'::jsonb ? (specs->>'spec_options')

But now that it's an array, I think I need to use the |? operator, and I can't work out how to get the right side of this operation into the correct format. (I think it needs to be text[] but I'm not sure.)

Best Answer

You can use the @> operator.

From the docs:

@> Does the left JSON value contain within it the right value?

So in your case it would look something like this:

select * from products where specs @> '{"spec_options": ["a", "b", "c"]}';

Related Solutions

PostgreSQL – Filtering on Array Length Inside JSON

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).

One trick to overcome this might be changing the query slightly and creating a covering index. For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this column in the index:

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id) 
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

This will hopefully turn into an index-only scan (I tested this omitting the jsonb part, but you will be able to tell if it works).

PostgreSQL JSON – Query Against JSON Array

You can write the query simpler without CTE which probably would perform better.

select (jsonb_array_elements(transaction->'transactions')->'id')::text as trx_id 
from transactions;

SQL alchemy returns a list of tuples. This is the standard behaviour. A row in PostgreSQL is represented as a tuple. Even if you query a single columns it's going to be a tuple with one element. Hence the (u'ABC123',) representation.

If you need a list of the first column you can easily transform your resultset in python:

[r[0] for r in rowset.fetchall()]

This should return

['ABC123', 'DEF456', 'GHI789']

Best Answer

Related Solutions

PostgreSQL – Filtering on Array Length Inside JSON

PostgreSQL JSON – Query Against JSON Array

Related Question