How can I convert this Json array in SQL Server?-CodePudding

I have an array like this and I want to extract its count into a single array. Can I do this in SQL Server?

[
  {
    "ItemName": "ITEM1",
    "Details": [
      {
        "column1": "2021\/Apr",
        "Count": 10
      },
      {
        "column1": "2021\/May",
        "count": 20
      },
      {
        "column1": "2021\/Jun",
        "count": 30
      }
    ]
  },
  {
    "ItemName": "ITEM2",
    "Details": [
      {
        "column1": "2021\/Apr",
        "count": 10
      },
      {
        "column1": "2021\/May",
        "count": 25
      },
      {
        "column1": "2021\/Jun",
        "count": 2
      }
    ]
  }
]

I want to convert this as following. How can I achieve this using SQL Server?

[
  {
    "ItemName": "ITEM1",
    "Details": [10, 20, 30]
  },
  {
    "ItemName": "ITEM2",
    "Details": [10, 25, 2]
  }
]

CodePudding user response：

It ain't pretty but:


DECLARE @json nvarchar(max) = N'
[
  {
    "ItemName": "ITEM1",
    "Details": [
      {
        "column1": "2021\/Apr",
        "count": 10
      },
      {
        "column1": "2021\/May",
        "count": 20
      },
      {
        "column1": "2021\/Jun",
        "count": 30
      }
    ]
  },
  {
    "ItemName": "ITEM2",
    "Details": [
      {
        "column1": "2021\/Apr",
        "count": 10
      },
      {
        "column1": "2021\/May",
        "count": 25
      },
      {
        "column1": "2021\/Jun",
        "count": 2
      }
    ]
  }
]'

SELECT  JSON_MODIFY(    -- We want to modify Details part inside the Item array
        x.value -- Contains whole json
    ,   '$.Details'  -- Path to the field we wanna replace
    ,   JSON_QUERY(  -- Value we replace with. It's important to use JSON_QUERY, otherwise SQL Server will create a string
            '['  -- Array start
              STUFF( -- STUFF part is is to remove to first "," from the ,10,20,30 string
                (   -- This monstrosity builds a comma separated list of count strings
                    SELECT  ','   cast(JSON_VALUE(value, '$.count') AS NVARCHAR(MAX))
                    FROM    OPENJSON(x.value, '$.Details') -- This iterates the elements under details
                    ORDER BY CAST([key] AS INT) -- We want to preserve sorting of the counts
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') -- This sucker gets a "text" from XML as a string
                , 1, 1, '') 
              ']' -- Finally end array
        )
    )
FROM    OPENJSON(@json) x -- This iterates the array and creates one row per "ItemName"

I think in newer versions (like SQL Server 2022), you can create arrays in simpler ways (https://techcommunity.microsoft.com/t5/azure-sql-blog/announcing-json-enhancements-in-azure-sql-database-azure-sql/ba-p/3417071)

CodePudding user response：

Try this

DECLARE @json nvarchar(max) = N'
[
  {
    "ItemName": "ITEM1",
    "Details": [
      {
        "column1": "2021\/Apr",
        "count": 10
      },
      {
        "column1": "2021\/May",
        "count": 20
      },
      {
        "column1": "2021\/Jun",
        "count": 30
      }
    ]
  },
  {
    "ItemName": "ITEM2",
    "Details": [
      {
        "column1": "2021\/Apr",
        "count": 10
      },
      {
        "column1": "2021\/May",
        "count": 25
      },
      {
        "column1": "2021\/Jun",
        "count": 2
      }
    ]
  }
]'

SELECT      x.ItemName
            , JSON_QUERY('['   STRING_AGG(s.count, ',') WITHIN GROUP (ORDER BY s.Column1)   ']') AS Details
FROM        OPENJSON(@json)
WITH        (
            ItemName    NVARCHAR(MAX)   '$.ItemName'
            , Details   NVARCHAR(MAX)   '$.Details' AS JSON
            ) x
CROSS APPLY OPENJSON(x.Details)
WITH        (
            Column1     NVARCHAR(MAX)   '$.column1'
            , Count     INT             '$.count'
            ) s
GROUP BY    x.ItemName
FOR JSON PATH

The idea is that you can CROSS APPLY more OPENJSON on columns parsed with AS JSON

To produce an array of scalars just concatenate counts with comma delimiter ordered by column1, wrap in square brackets and mark with JSON_QUERY not to be treated as a string but as a full-bloodied JSON array by final FOR JSON PATH.