0

I've broken this down with executable code. I need to get the lang and value columns into a single record. Any ideas on how to go about this:

DECLARE
@JSONMain NVARCHAR(1000)

SET @JSONMain = N'{ "problemtype_data": [
        {
            "description": [
                {
                    "lang": "en",
                    "value": "CWE-200"
                },
                {
                    "lang": "en",
                    "value": "NVD-CWE-noinfo"
                }
            ]
        }
    ]
}'

SELECT  Problem.ProblemType, Problem.ProblemTypeLang
FROM OPENJSON(@JSONMain, '$.problemtype_data')
WITH (
    PrblmTypeJSON NVARCHAR(max) '$.description' as JSON
) ProblemType
OUTER APPLY OPENJSON(ProblemType.PrblmTypeJSON, '$')
WITH (
    ProblemType NVARCHAR(15) '$.value',
    ProblemTypeLang NVARCHAR(5) '$.lang'
) Problem

The executed code produces:
enter image description here

I would like to see it like this:
ProblemType:                                  ProblemTypeLang:
CWE-200, NVD-CWE-noinfo            en,en

1 Answer 1

1

You can aggregate the values using STRING_AGG inside an APPLY. If you want to get the values in the order they were in the array, you need another OPENJSON without a schema, in order to get the index.

SELECT
  p.ProblemType, p.ProblemTypeLang
FROM OPENJSON(@JSONMain, '$.problemtype_data')
  WITH (
    description nvarchar(max) AS JSON
  ) ProblemType
CROSS APPLY(
    SELECT
      ProblemType = STRING_AGG(Problem.lang, ', ') WITHIN GROUP (ORDER BY v.[key]),
      ProblemTypeLang = STRING_AGG(Problem.value, ', ') WITHIN GROUP (ORDER BY v.[key])
    FROM OPENJSON(ProblemType.description) array
    CROSS APPLY OPENJSON(array.value)
      WITH (
        value nvarchar(15),
        lang nvarchar(5)
      ) Problem
    CROSS APPLY (SELECT CAST(array.[key] AS int)) v([key])
) p;

db<>fiddle

The extra CROSS APPLY to convert the index was due to this bug in SQL Server which was appearing, see fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.