How can I query a dictionary in a database table to filter for rows given a constant?

by

I have the following database table, let’s call it features:

 id      date       info 
 1   2024-02-15    {'ccy':'CHF', 'mic':'blvd', 'market_type': 56}   
 2   2024-02-15    {'ccy':'AUD', 'mic':'kkl', 'market_type': 33}
 ..

I would like to filter the rows where the market_type is not in a given list. Let’s say the list is [1,2,3,4,56]

My query reads something like this:

select * from features where info ->> 'market_type' not in (1,2,3,4,56)

The above throws a syntax error. Any ideas what the right query is?

  • Try this : SELECT * FROM features WHERE (info->>’market_type’)::integer NOT IN (1, 2, 3, 4, 56);

    – 




  • 1

    Thanks you, that works! One more question please – is it possible instead of returning the whole rows, to return the ‘market_type’ instead?

    – 

  • a syntax error, would be nice to post the error message next time.

    –  

SELECT *
FROM features
WHERE CAST(info ->> 'market_type' AS INTEGER) NOT IN (1, 2, 3, 4, 56);

Here this CAST(info ->> ‘market_type’ AS INTEGER) is used to convert the value of market_type from a string to an integer.

Leave a Comment