Retrieve specific value from a JSON blob in MS SQL Server, using a property value?

Question

In my DB I have a column storing JSON. The JSON looks like this:

{
  "views": [
    {
      "id": "1",
      "sections": [
        {
          "id": "1",
          "isToggleActive": false,
          "components": [
            {
              "id": "1",
              "values": [
                "02/24/2021"
              ]
            },
            {
              "id": "2",
              "values": []
            },
            {
              "id": "3",
              "values": [
                "5393",
                "02/26/2021 - Weekly"
              ]
            },
            {
              "id": "5",
              "values": [
                ""
              ]
            }
          ]
        }
      ]
    }
  ]
}

I want to create a migration script that will extract a value from this JSON and store them in its own column.

In the JSON above, in that components array, I want to extract the second value from the component with an ID of "3" (among other things, but this is a good example). So, I want to extract the value "02/26/2021 - Weekly" to store in its own column.

I was looking at the JSON_VALUE docs, but I only see examples for specifing indexes for the json properties. I can't figure out what kind of json path I'd need. Is this even possible to do with JSON_VALUE?

EDIT: To clarify, the views and sections components can have static array indexes, so I can use views[0].sections[0] for them. Currently, this is all I have with my SQL query:

SELECT
    *
FROM OPENJSON(@jsonInfo, '$.views[0].sections[0]')

Charlieface · Accepted Answer · 2021-03-04 20:21:26Z

1

You need to use OPENJSON to break out the inner array, then filter it with a WHERE and finally select the correct value with JSON_VALUE

SELECT
    JSON_VALUE(components.value, '$.values[1]')
FROM OPENJSON (@jsonInfo, '$.views[0].sections[0].components') components
WHERE JSON_VALUE(components.value, '$.id') = '3'

answered Mar 4, 2021 at 20:21

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

Retrieve specific value from a JSON blob in MS SQL Server, using a property value?

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