CODEWITHSUNDEEP
sqljsonsql-serversql-server-2016
Sharad
Sharad

Reputation: 1252

Concatenate nested JSON in SQL Server

I am trying to concatenate nested JSON in SQL, how to do that.

Please help me out with this.

Example: I have JSON Like below.

    [
  {
    "Name": "Cards",
    "Value": [
      "Pack of 24 Dare Cards"
    ],
    "SourceSpecified": false,
    "Any": []
  },
  {
    "Name": "Boppers and Shot Glasses",
    "Value": [
      "12 Willy Shot Glass and 12 Hen Boppers"
    ],
    "SourceSpecified": false,
    "Any": []
  }
]

I want Out Put:

Pack of 24 Dare Cards-12 Willy Shot Glass and 12 Hen Boppers

Upvotes: 3

Views: 1066

Answers (2)

Sharad
Sharad

Reputation: 1252

This one I achieved with creating a scalar-valued function to populate concatenated value.

Create FUNCTION  [dbo].[test] 
(
    @VariationData nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
                          Declare @val nvarchar(max)
                          select @val = COALESCE(@val+', ','')+ Value from  OPENJSON(@VariationData)
                          With ([Value] NVARCHAR(MAX) N'$.Value[0]') 
                          return @val

END

Call function with nested JSON value i.e

Select  [dbo].[test] (@JSON);

This worked for me.

Upvotes: 1

qxg
qxg

Reputation: 7036

If Value always contains one element

DECLARE @JSON NVARCHAR(MAX) = '    [
  {
    "Name": "Cards",
    "Value": [
      "Pack of 24 Dare Cards"
    ],
    "SourceSpecified": false,
    "Any": []
  },
  {
    "Name": "Boppers and Shot Glasses",
    "Value": [
      "12 Willy Shot Glass and 12 Hen Boppers"
    ],
    "SourceSpecified": false,
    "Any": []
  }
]'

SELECT STRING_AGG(Val, '-')
FROM OPENJSON(@JSON)
WITH (Val NVARCHAR(MAX) '$.Value[0]')

Support of multiple values is also easy.

SELECT STRING_AGG(Val, '-')
FROM OPENJSON(@JSON)
WITH (VALUE NVARCHAR(MAX) '$.Value' AS JSON)
OUTER APPLY OPENJSON(VALUE) WITH (Val NVARCHAR(MAX) '$')

Upvotes: 1

Related Questions