CODEWITHSUNDEEP
snowflake-cloud-data-platform
Abhi
Abhi

Reputation: 89

Extracting data from JSON column defined as String

A table has a ports column (defined as VARCHAR) which has the following data:

[{u'position': 1, u'macAddress': u'00:8C:FA:C1:7C:88'}, {u'position': 2, u'macAddress': u'00:8C:FA:5E:98:81'}]

I want to extract the data from just the macAddress fields into separate rows. I tried to flatten the data in Snowflake but it is not working as the column is not defined as VARIANT and the the fields have a 'u' in front of them (this is my guess).

00:8C:FA:C3:7C:84
00:5C:FA:7E:98:87

Could someone please help with the requirement.

Upvotes: 1

Views: 39

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

The provided JSON is not a valid JSON but it is possible to treat it as one with text operations and PARSE_JSON:

SELECT s.value:macAddress::TEXT AS macAddress
FROM t
,LATERAL FLATTEN(INPUT => PARSE_JSON(REPLACE(REPLACE(col, 'u''', ''''), '''', '"'))) 
                 AS s;

For input:

CREATE OR REPLACE TABLE t(col TEXT)
AS
SELECT $$[{u'position': 1, u'macAddress': u'00:8C:FA:C1:7C:88'}, {u'position': 2, u'macAddress': u'00:8C:FA:5E:98:81'}]$$;

Output:

enter image description here

Upvotes: 2

Related Questions