Query a single element from a dictionary-like column in SQL-CodePudding

I have a PostgresSQL table that looks as follows:

id             order_id   products
[PK] integer   integer    character varying
1              123        {"type": "foo", "counts": 2}
2              456        {"type": "foobar", "counts": 4}
3              789        {"type": "foo", "counts": 1}
4              678        {"type": "baz", "counts": 3}

I would like to query for only where type = foo.

In another query, I've successfully used the following:

SELECT 
    table_a.data::json->>'type' prod_type,
    
FROM table.a

But, this only works because the data column is JSON type.

How would I index into the products column such that I only return type = foo?

1              123        {"type": "foo", "counts": 2}
3              789        {"type": "foo", "counts": 1}

Thanks!

CodePudding user response：

You can try something like this:

SELECT *
FROM test
WHERE products::json->>'type' = 'foo';

Sample Output: