How to extract values from JSON array field?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-13-2023 11:54 PM
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-14-2023 12:01 AM
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'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-14-2023 12:02 AM
select *, PersonalPhones:[0].value as Value from <table_name>
This can give you in the array format
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-14-2023 09:26 AM
Maybe I didn't explain it correctly. The JSON snippet from the description is a cell from a row from a table.