Concatenate nested JSON in SQL Server

Question

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

Will Value only contain one element?

qxg
– qxg

2018-08-09 08:10:13 +00:00
Commented Aug 9, 2018 at 8:10 — qxg
– qxg, Commented Aug 9, 2018 at 8:10
Yes only one element.

Sharad
– Sharad

2018-08-09 08:16:57 +00:00
Commented Aug 9, 2018 at 8:16 — Sharad
– Sharad, Commented Aug 9, 2018 at 8:16

qxg · Accepted Answer · 2018-08-09 08:20:30Z

1

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) '$')

edited Aug 9, 2018 at 8:20

answered Aug 9, 2018 at 8:13

qxg

7,0961 gold badge31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sharad Over a year ago

'STRING_AGG' is not a recognized built-in function name

Robert Over a year ago

STRING_AGG need SqlServer 2017 as min version

qxg Over a year ago

You tagged sql-server-2017.

Sharad Over a year ago

Sorry mine is sql server 2016

qxg Over a year ago

There are plenty of string join solutions prior SQL Server 2017. Just search it. I think your core question is json instead of string join.

|

Sharad · Accepted Answer · 2018-08-13 07:35:21Z

1

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.

answered Aug 13, 2018 at 7:35

Sharad

1,2721 gold badge10 silver badges23 bronze badges

Collectives™ on Stack Overflow

Concatenate nested JSON in SQL Server

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related