Get first N array elements from unnest + string_to_array function

Question

I would like to access first 5 elements of an array "ingredients" before UNNESTing the array:

SELECT 
    TRIM
    (REGEXP_REPLACE
        (UNNEST
            (string_to_array(ingredients, ';'))[1:5], '\S+\%|\s+\(.*?\)','','g'))
FROM products

The "Ingredients" array may contain values such as:

{'1%', 'abc(2%)', 'abcd', ... , N th element}

The code below works fine but I don't know what's wrong with the above code.

SELECT (string_to_array('1,2,3,4',','))[1:3]

Nick · Accepted Answer · 2017-08-09 00:44:20Z

1

This works:

select
  trim(
    regexp_replace(
      unnest(
        (string_to_array(ingredients, ';'))[1:5]
      ),
      '\S+\%|\s+\(.*?\)',
      '',
      'g'
    )
  )
from products;

Your example shows how important is to maintain good formatting/indentation.

The following is the same as what you wrote, but re-formatted (opening parenthesis is next to function itself, closing one is at the same indentation level, anything inside is indented more):

SELECT 
    TRIM(
        REGEXP_REPLACE(
           UNNEST(
               string_to_array(ingredients, ';')
           )[1:5],
           '\S+\%|\s+\(.*?\)',
           '',
           'g'
        )
    )
FROM products

-- the mistake is obvious, you're trying to apply [1:5] to an already unnested result set, not to an array.

edited Aug 9, 2017 at 0:44

answered Aug 9, 2017 at 0:30

Nick

2,54315 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Get first N array elements from unnest + string_to_array function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related