extract value from JSON in sql server

Question

[
    {
        "Levelname": "LGA",
        "levelvalue": "Alimosho"
    },
    {
        "Levelname": "STATE",
        "levelvalue": "LAGOS"
    },
    {
        "Levelname": "SUB ZONE",
        "levelvalue": "LAGOS 2"
    },
    {
        "Levelname": "BRANCH",
        "levelvalue": "LAGOS 2"
    },
    {
        "Levelname": "ZONE",
        "levelvalue": "LAGOS"
    }
]

is there a way we can extract this data from JSON and put in the table format like:

Lga    STATE     SUB ZONE    BRANCH   ZONE

There's plenty of questions on Stack Overflow on how to consume JSON; what didn't you understand about them? What have you tried so far? Why didn't your attempts work? — Thom A
– Thom A ♦, Commented Jul 23, 2021 at 14:47
is there a way we can extract this data from JSON? where is the json? in JavaScript? C# , OR it is hard code in sql? — Alireza Ahmadi
– Alireza Ahmadi, Commented Jul 23, 2021 at 14:51
@AlirezaAhmadi we stored this data in table and want to extract from there. — Sandeep Singh
– Sandeep Singh, Commented Jul 23, 2021 at 14:52
see this: red-gate.com/simple-talk/sql/t-sql-programming/… — Alireza Ahmadi
– Alireza Ahmadi, Commented Jul 23, 2021 at 14:54

Charlieface · Accepted Answer · 2021-07-23 16:09:57Z

2

You need to explode out the array with OPENJSON and JSON_VALUE, then pivot the values back up into a single row

You could also use OPENJSON again instead of JSON_VALUE, and you could place the pivot on the outside if you prefer

SELECT j.*
FROM YourTable t
CROSS APPLY (
    SELECT *
    FROM (
        SELECT
            levelvalue = JSON_VALUE(j.value, '$.levelvalue'),
            Levelname =  JSON_VALUE(j.value, '$.Levelname')
        FROM OPENJSON(t.JsonColumn) j
    ) j
    PIVOT (
      MAX(levelvalue) FOR Levelname IN
        (LGA, STATE, [SUB ZONE], BRANCH, ZONE)
    ) pvt
) j;

answered Jul 23, 2021 at 16:09

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

extract value from 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