CODEWITHSUNDEEP
sqlarraysjsonsql-server
PovilasZ
PovilasZ

Reputation: 301

Get value from array in JSON in SQL Server

Let's say we have this json in our database table. I want to select value from tags. I already know how to get an array from this data but I don't know how to access array members. The question would be how do I get the first value from the array? Is there a function for this task?

{
    "info": {
        "type": 1,
        "address": {
            "town": "Bristol",
            "county": "Avon",
            "country": "England"
        },
        "tags": ["Sport", "Water polo"]
    },
    "type": "Basic"
}

Query I already have:

SELECT JSON_QUERY(MyTable.Data, '$.info.tags')
FROM MyTable

This returns me:

["Sport", "Water polo"]

How do I get

Sport

Upvotes: 5

Views: 20848

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

JSON_QUERY returns an object or array. You need JSON_VALUE to return a scalar value, eg :

SELECT JSON_VALUE(Data, '$.info.tags[0]')
from MyTable

Check the section Compare JSON_VALUE and JSON_QUERY in the docs for more examples

Upvotes: 6

Related Questions