topic Re: How to extract values from JSON array field? in Data Engineering

How to extract values from JSON array field?

vicusbass — Fri, 14 Apr 2023 06:54:50 GMT

Hi,

While creating an SQL notebook, I am struggling with extracting some values from a JSON array field. I need to create a view where a field would be an array with values extracted from a field like the one below, specifically I need the `value` field.

[{"sources": [{"providerIds": ["xx"], "source": "vendor", "status": "Verified", "type": "PersonalMobile", "updatedAt": {"$date": 1606563305398}}], "status": "Verified", "type": "PersonalMobile", "updatedAt": {"$date": 1606563305398}, "value": "+1-555-555"}, {"sources": [{"providerIds": ["xx"], "source": "vendor", "status": "Verified", "type": "PersonalMobile"}], "status": "Verified", "type": "PersonalMobile", "updatedAt": {"$date": 1606563305398}, "value": "+1-666-666"}]

I tried a few things:

SELECT [*].value from personalPhones

SELECT from_json(personalPhones:[*].value, 'string')

SELECT from_json(personalPhones:[*], 'value string'

It's syntax error OR invalid schema.

Any suggestions?

Re: How to extract values from JSON array field?

Avinash_94 — Fri, 14 Apr 2023 07:01:55 GMT

For doing a search in a JSON array, one needs to use

OPENJSON

DECLARE @table TABLE (Col NVARCHAR(MAX))

INSERT INTO @table VALUES ('{"names":["Joe","Fred","Sue"]}')

SELECT * FROM @table

WHERE 'Joe' IN ( SELECT value FROM OPENJSON(Col,'$.names'))

Or as an alternative, one can use it with

CROSS APPLY

SELECT * FROM

@table

CROSS APPLY OPENJSON(Col,'$.names')

WHERE value ='Joe'

Re: How to extract values from JSON array field?

User16756723392 — Fri, 14 Apr 2023 07:02:11 GMT

select *, PersonalPhones:[0].value as Value from <table_name>

This can give you in the array format

Re: How to extract values from JSON array field?

vicusbass — Fri, 14 Apr 2023 16:26:46 GMT

Maybe I didn't explain it correctly. The JSON snippet from the description is a cell from a row from a table.