Question 110910 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How can I convert this Json array in SQL Server?

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]
  }
]
Tags:

2

Answers


  1. 0

    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)

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search