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

Answer 1

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;