Postgres how to find rows where in jsonb type column contains key with array which contain some value with or condition

Question

I use PostgreSQL 11.8 and faced task, where I need save many sizes for product and need search by sizes too. Before have structure without nested, only one level, without multiply SIZE key

{"SIZE": "98/104", "COLOUR": "Korall", "GENDER": "female", "CURRENCY": "SEK", "AGE_GROUP": "Kids", "ALTERNATIVE_IMAGE": "https://i1.adis.ws/i/Lindex/8016913_8414_PS_MF?w=300&h=300"}

and search I did like that

         WHERE ((products_alias.extras @> '{"SIZE":"One Size"}' 
                     OR products_alias.extras @> '{"SIZE":"146/152"}')                       
                     AND (products_alias.extras @> '{"COLOUR":"Flerfärgat"}' 
                     OR products_alias.extras @> '{"COLOUR":"Grå"}'
                     ))

But now I need provide opportunity save multiply SIZE and refactor search for that. Example if data will be look like that

{"SIZE": ["38", "90", "12", "4"], "COLOUR": "Svart", "GENDER": "female", "CURRENCY": "SEK", "AGE_GROUP": "Adult", "ALTERNATIVE_IMAGE": "https://i1.adis.ws/i/Lindex/7962770_80_PS_MF?w=300&h=300"}

how build search query for search by SIZE key with OR condition. Like search all where SIZE can contain value 38 or value 4 ?

Bergi · Accepted Answer · 2020-06-09 10:54:40Z

2

The @> operator works on nested structures as well. To check whether the array contains 38 or 4, you can use

products_alias.extras @> '{"SIZE":["4"]}' OR products_alias.extras @> '{"SIZE":["38"]}'

To check whether the array contains both, you could use

products_alias.extras @> '{"SIZE":["4","38"]}'

edited Jun 9, 2020 at 10:54

user330315

answered Jun 9, 2020 at 10:49

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-06-09 10:49:48Z

1

You could put the searched values in an array of texts, and use the ?| operator:

where extras -> 'SIZE' ?| array['38', '4']

This checks whether the nested JSONB array on the left side of the operator contains any of the values in the text array on the right side.

If you want the JSONB array to contain all the target values, then use ?& instead of ?|.

answered Jun 9, 2020 at 10:49

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Postgres how to find rows where in jsonb type column contains key with array which contain some value with or condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related