Search code examples
jsonsql-serveropen-json

SQL Parse Json array to rows

I'm trying to parse the 'custinfo' array to rows, rather than specific columns how I have in my query (there can be none or many values in the array)

     DECLARE @json NVARCHAR(MAX) ='{
  "customer": [
    {
      "id": "123",
      "history": [
        {
          "id": "a123",
          "dates": [
            {
              "date": "2022-03-19",
              "details": {
                "custinfo": [
                  "male",
                  "married"
                ],                
                "age": 40            
                }}]}]}]}'
                                             
SELECT
    JSON_VALUE ( j.[value], '$.id' ) AS CustId,
  JSON_VALUE ( m.[value], '$.id' ) AS CustId_Hist,
   JSON_VALUE ( a1.[value], '$.date' ) AS date,
   JSON_VALUE ( a1.[value], '$.details.age' ) AS age,
   JSON_VALUE ( a1.[value], '$.details.custinfo[0]' ) AS custinfo0,
   JSON_VALUE ( a1.[value], '$.details.custinfo[1]' ) AS custinfo1
FROM OPENJSON( @json, '$."customer"' ) j
 CROSS APPLY OPENJSON ( j.[value], '$."history"' ) AS m
  CROSS APPLY OPENJSON ( m.[value], '$."dates"' ) AS a1

Desired results:

enter image description here

Solution

  • Like I mentioned in the comments, I would switch to using WITH clauses and defining your columns and their data types. You can then also get the values, into 2 separate rows you want, with the following. Note tbhe extra OPENJSON at the end, which treats the custinfo as the array it is; returning 2 rows (1 for each value in the array):

    DECLARE @json NVARCHAR(MAX) ='{
  "customer": [
    {
      "id": "123",
      "history": [
        {
          "id": "a123",
          "dates": [
            {
              "date": "2022-03-19",
              "details": {
                "custinfo": [
                  "male",
                  "married"
                ],                
                "age": 40            
                }}]}]}]}';
                                             
SELECT c.id AS CustId,
       h.id AS CustId_Hist,
       d.date AS date,
       d.age AS age,
       ci.[value] AS custinfo
FROM OPENJSON( @json,'$.customer')
     WITH (id int,
           history nvarchar(MAX) AS JSON) c
     CROSS APPLY OPENJSON (c.history)
                 WITH (id varchar(10),
                       dates nvarchar(MAX) AS JSON) h
     CROSS APPLY OPENJSON (h.dates) 
                 WITH(date date,
                      details nvarchar(MAX) AS JSON,
                      age int '$.details.age') d
     CROSS APPLY OPENJSON(d.details,'$.custinfo') ci;