Regex in Amazon Redshift using SQL

Question

I'm using sql in amazon redshift. I have a column called attributes which looks like this:

[{"name": "Size", "value": "Small"},{"name": "Color", "value": "Blue"},{"name": "Material", "value": "Cotton"}]

or

[{"name": "Material", "value": "silk"},{"name": "Color", "value": "Pink"}]

In order to extract the material value, I'm trying to use regex. I have the initial code such:

select REGEXP_SUBSTR(attributes,'Material.*') as string_value

Result:

Material", "value": "Cotton"}]

and

Material", "value": "silk"},{"name": "Color", "value": "Pink"}]

How can I take this a step further and get just the value of material, so output should look like:

Cotton

silk

I'm new to sql and new to regex so would really appreciate your help.

score 1 · Answer 1 · answered Jun 22 '21 at 15:33

1

You can use regular expressions and capture groups:

select REGEXP_SUBSTR(attributes, '"Material", "value": "([^"]*)"') as string_value

answered Jun 22 '21 at 15:33

Gordon Linoff

1,242,037
58
646
786

I modified your suggestion to this: select REGEXP_SUBSTR(attributes, '"Material", "value": "([^"]*)"') as string_value , REGEXP_SUBSTR(string_value, ':.*') as material_value. Example result is : "Cotton" How could I remove the : and the two " to get just Cotton? Thanks! – Sluna Jun 22 '21 at 15:50

score 1 · Answer 2 · answered Jun 22 '21 at 15:43

You should probably not be resorting to using regex to parse JSON content, especially given that your JSON content is nested inside an array. I am admittedly not an expert in the Redshift JSON API, but this SO question was very helpful. Consider the following approach:

CREATE VIEW seq_0_to_3 AS
    SELECT 0 AS i UNION ALL                                      
    SELECT 1 UNION ALL
    SELECT 2          
);

WITH exploded_array AS (                                                                          
    SELECT id, JSON_EXTRACT_ARRAY_ELEMENT_TEXT(json_col, seq.i) AS json
    FROM yourTable, seq_0_to_3 AS seq
    WHERE seq.i < JSON_ARRAY_LENGTH(json_col)
)

SELECT JSON_EXTRACT_PATH_TEXT(json, 'value') AS Material
FROM exploded_array
WHERE JSON_EXTRACT_PATH_TEXT(json, 'name') = 'Material';

This answer assumes that your JSON arrays would always have 3 elements for the size, color, and material. If the count would differ from 3, then update the view seq_0_to_3 above with the correct number of elements.

Regex in Amazon Redshift using SQL

2 Answers2