Get value from array in JSON in SQL Server

Question

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

Panagiotis Kanavos · Accepted Answer · 2018-08-01 12:19:51Z

6

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

edited Aug 1, 2018 at 12:19

answered Aug 1, 2018 at 12:16

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get value from array in JSON in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related