Postgresql 9.5 JSONB nested arrays LIKE statement

Question

I have a jsonb column, called "product", that contains a similar jsonb object as the one below. I'm trying to figure out how to do a LIKE statement against the same data in a postgresql 9.5.

{
  "name":"Some Product",
  "variants":[
    {
      "color":"blue",
      "skus":[
        {
          "uom":"each",
          "code":"ZZWG002NCHZ-65"
        },
        {
          "uom":"case",
          "code":"ZZWG002NCHZ-65-CASE"
        },
      ]
    }
]}

The following query works for exact match.

SELECT * FROM products WHERE product#> '{variants}' @> '[{"skus":[{"code":"ZZWG002NCHZ-65"}]}]';

But I need to support LIKE statements like "begins with", "ends width" and "contains". How would this be done?

Example: Lets say I want all products returned that have a sku code that begins with "ZZWG00".

klin · Accepted Answer · 2016-06-02 21:26:11Z

2

You should unnest variants and skus (using jsonb_array_elements()), so you could examine sku->>'code':

SELECT DISTINCT p.* 
FROM 
    products p,
    jsonb_array_elements(product->'variants') as variants(variant),
    jsonb_array_elements(variant->'skus') as skus(sku)
WHERE
    sku->>'code' like 'ZZW%';

Use DISTINCT as you'll have multiple rows as a result of multiple matches in one product.

answered Jun 2, 2016 at 21:26

klin

123k15 gold badges240 silver badges262 bronze badges

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

4 Comments

jfutch Over a year ago

Will this still take advantage of indexes?

klin Over a year ago

Still? There is no word about indexes in the question. What indexes do you mean?

jfutch Over a year ago

My apologies.. I'm using.. CREATE INDEX on products USING GIN (product jsonb_path_ops);

klin Over a year ago

No, the index will not be used as it supports only @> operator. Note however, that you are going to use like, in which case such indexes are useless.

Collectives™ on Stack Overflow

Postgresql 9.5 JSONB nested arrays LIKE statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related