Data population from database as nested array object

Question

I have a table structure as below in SQL Server database,

I want to populate the data from database something similar to JSON like as below:

id: 1
aname:  xyz
categories: bus
{
arnam: res
street: [s1,s2]
},
{
arnam: com
street: [c1,c2]
}

Can someone please guide me as to how I can do this in the database using normal SQL query or procedure.

Answer 1

Your json is not valid, but according to your table I think you want to know how to parse data from nested JSON with array of values with this structure:

WITH cte AS (
SELECT * FROM (VALUES
('{"id": 1, "aname":  "xyz",
   "categories": {
      "bus": [
         {"aname":  "res",
          "street": ["c1", "c2"]
          },
         {"aname":  "res",
          "street": ["s1", "s2"]
         }]
        }
    }'),
('{"id": 2, "aname":  "abc",
   "categories": {
      "bus": [
         {"aname":  "foo",
          "street": ["c1", "c2"]
          },
         {"aname":  "zoo",
          "street": ["s1", "s2"]
         }]
        }
    }')
) t1 ([json])
)SELECT
    ROW_NUMBER() OVER(ORDER BY [id]) AS RN,
    *
FROM cte AS e
CROSS APPLY OPENJSON(e.[json]) WITH (
    [id] int '$.id',
    [aname] VARCHAR(100) '$.aname',
    [categories_jsn] NVARCHAR(MAX) '$.categories.bus' AS JSON
) AS jsn
CROSS APPLY OPENJSON([categories_jsn]) WITH (
    [street_arr] NVARCHAR(MAX) '$.street' AS JSON,
    [aname_lvl2] VARCHAR(20) '$.aname'
) AS jsn2
CROSS APPLY OPENJSON([street_arr]) WITH (
    [street] VARCHAR(20) '$'
)

Output: