cancel
Showing results for 
Search instead for 
Did you mean: 

How to extract values from JSON array field?

vicusbass
New Contributor II

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?

3 REPLIES 3

Avinash_94
New Contributor III

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
New Contributor III

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

This can give you in the array format

vicusbass
New Contributor II

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

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.