How to extract values from JSON array field?

[vicusbass]

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?

[Avinash_94]

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'

[User16756723392]

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

This can give you in the array format

[vicusbass]

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