How to extract values from a nested json field in sql server

by

Extracting values from a nested JSON field in SQL Server

To extract values from a nested JSON field in SQL Server, you can make use of the JSON functions provided by SQL Server. These functions allow you to parse and navigate JSON data within a SQL query.

Let’s assume you have a table named “myTable” with a column named “jsonData” that stores the nested JSON data. Here’s an example of how you can extract values from the nested JSON field: 


    -- Sample JSON data
    DECLARE @json NVARCHAR(MAX) = '
    {
      "person": {
        "name": "John",
        "age": 30,
        "address": {
          "street": "123 Main St",
          "city": "New York",
          "state": "NY"
        }
      }
    }'

    -- Extracting values from the nested JSON field
    SELECT
      JSON_VALUE(@json, '$.person.name') AS Name,
      JSON_VALUE(@json, '$.person.age') AS Age,
      JSON_VALUE(@json, '$.person.address.street') AS Street,
      JSON_VALUE(@json, '$.person.address.city') AS City,
      JSON_VALUE(@json, '$.person.address.state') AS State

The JSON_VALUE function is used to extract a single scalar value from the JSON data. In the example above, we extract the values for “name”, “age”, “street”, “city”, and “state” from the nested JSON field.

If you want to extract an entire nested JSON object or array, you can use the JSON_QUERY function instead. Here’s an example: 


    -- Extracting the entire nested JSON object
    SELECT JSON_QUERY(@json, '$.person') AS Person

The JSON_QUERY function returns the specified JSON object or array as a result. In the example above, we extract the entire “person” object from the nested JSON field.

Leave a comment