String aggregation using JSON in SQL Server 2016

Question

I would like to format a json string '[{"_":7},{"_":13},{"_":17}]' as '[7,13,17]' Tried with REPLACE Method in TSQL. I have to use REPLACE method three times to get the desire result.

SELECT REPLACE(REPLACE(REPLACE('[{"_":7},{"_":13},{"_":17}]','},{"_":',', '),'{"_":',''),'}','')

is there a better way to do that? I am using SQL Server 2016.

After some comments for this post, This my actual issue.

I have some customer data. Customer Table

CustomerId | Name
    1         ABC
    2         XYZ
    3         EFG

each customer has some area of interest. Customer Area of Interest

CustomerAreaInterestId | FK_CustomerId | FK_AreaOfInterestId
      1                       1            2
      2                       1            3
      3                       1            5
      4                       2            1
      5                       2            2
      6                       3            3
      7                       3            4

Area of interest table

   AreaOfInterestId | Description
       1                Interest1
       2                Interest2
       3                Interest3
       4                Interest4
       5                Interest5

In the final result set, I have to include area of interest id's as an array of value

[
{
    "CustomerName": "ABC",
    "AreaofInterest": "[2,3,5]"
},
{
    "CustomerName": "XYZ",
    "AreaofInterest": "[1,2]"
},
{
    "CustomerName": "EFG",
    "AreaofInterest": "[3,4]"
}
]

The result consists with some other data’s as well. I have omitted for the code brevity.

You are neither formatting nor replacing. You're querying an array of objects, extracting the property values and returning them as an array themselves. You need to write a query for this — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 27, 2019 at 9:11
@PanagiotisKanavos I was follwing his approch to generate that output. stackoverflow.com/a/42778050/764369 — Arooran
– Arooran, Commented Nov 27, 2019 at 9:17
This has nothing to do with JSON. You're asking how to aggregate strings in SQL Server 2016. This answer to that question shows the correct query, although it's not that different from the common XML method. No replacement needed, — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 27, 2019 at 9:21
Post the actual problem, actual data and actual output. If you have a problem with that query's results, adding extra REPLACE statements won't fix the problem. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 27, 2019 at 9:23
You need fewer replacements, not more. The linked answer works for string values, which are quoted. You're asking how to work with numeric values which aren't. You need to remove some quotes. Or just use the XML technique — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 27, 2019 at 9:29

Panagiotis Kanavos · Accepted Answer · 2019-11-27 09:57:29Z

2

Short Version

Cast the numeric field to text before trying to aggregate it

From the comments, it looks like the real question is how to use JSON to aggregate strings in SQL Server 2016, as shown in this answer.

SELECT 
 JSON_VALUE(
   REPLACE(
     (SELECT _ = someField FROM someTable FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
)

or, rewritten for clarity :

SELECT 
 JSON_VALUE(  REPLACE(
                    (SELECT _ = someField 
                     FROM someTable 
                     FOR JSON PATH)
              ,'"},{"_":"',', ')
 ,'$[0]._')

That query works only with string fields. One needs to understand what it does before it can be adopted to other types.

The inner query generates a JSON string from a field's values, eg '[{"_":"value1"},{"_":"value2"}]'.
REPLACE replaces the quotes and separators between objects, changing that array of objects to '[{"_":"value1,value2"}]'. That's a single object in an array, whose single attribute is a comma-separated string.
JSON_VALUE(...,,'$[0]._') extracts the _ attribute of that single array item.

That trick can't be used with numeric values because they don't have quotes. The solution is to cast them to text first:

SELECT 
 JSON_VALUE(  REPLACE(
                    (SELECT _ = CAST(someNumber as nvarchar(20))
                     FROM someTable 
                     FOR JSON PATH)
              ,'"},{"_":"',', ')
 ,'$[0]._')

Eg :

declare @t table (id int)
insert into @t 
values
(7),
(13),
(17)


SELECT 
   JSON_VALUE(   REPLACE(
                        (SELECT _ = cast(ID as nvarchar(20)) 
                         FROM @t 
                         FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._')

The only change from the original query is the cast clause.

This produces :

7, 13, 17

This conversion is localized so care must be taken with decimals and dates, to avoid producing unexpected results, eg 38,5, 40,1 instead of 38.5, 40.1.

PS: That's no different than the XML technique, except STUFF is used there to cut off the leading separator. That technique also needs casting numbers to text, eg :

SELECT STUFF(
    (  SELECT N', ' + cast(ID as nvarchar(20)) 
       FROM @t FOR XML PATH(''),TYPE)
    .value('text()[1]','nvarchar(max)'),
    1,2,N'')

edited Nov 27, 2019 at 9:57

answered Nov 27, 2019 at 9:43

Panagiotis Kanavos

134k16 gold badges211 silver badges271 bronze badges

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

2 Comments

Arooran Over a year ago

Very well explained. I have updated the question based on your request.

Reversed Engineer Over a year ago

In my testing with a big dataset, and including a WHERE clause in the SELECT statement to aggregate detail records per master record, the FOR JSON version is about 20% faster than FOR XML, and doesn't have issues with encoding special characters like & and > etc.

Zhorov · Accepted Answer · 2019-11-27 09:29:30Z

2

If you want to use only JSON functions (not string-based approach), the next example may help:

DECLARE @json nvarchar(max) = N'[{"_":7},{"_":13},{"_":17}]'
DECLARE @output nvarchar(max) = N'[]'

SELECT @output = JSON_MODIFY(@output, 'append $', j.item)
FROM OPENJSON(@json) WITH (item int '$."_"') j

SELECT @output AS [Result]

Result:

Result
[7,13,17]

Of course, the approach based on string aggregation is also a possible solution:

DECLARE @json nvarchar(max) = N'[{"_":7},{"_":13},{"_":17}]'

SELECT CONCAT(
   N'[',
   STUFF(
      (
      SELECT CONCAT(N',', j.item)
      FROM OPENJSON(@json) WITH (item int '$."_"') j
      FOR XML PATH('')
      ), 1, 1, N''
   ),
   N']'
)

edited Nov 27, 2019 at 9:29

answered Nov 27, 2019 at 9:19

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

1 Comment

Panagiotis Kanavos Over a year ago

The OP is already trying to use a string aggregation technique based on JSON

Gosfly · Accepted Answer · 2019-11-27 09:12:28Z

1

Yes you could do it with only 2 replace :

SELECT REPLACE(REPLACE('[{"_":7},{"_":13},{"_":17}]','{"_":',''),'}','')

DEMO HERE

Except if you really need a space after coma which is not what you asked to be honest.

answered Nov 27, 2019 at 9:12

Gosfly

1,2982 gold badges8 silver badges16 bronze badges

1 Comment

Reversed Engineer Over a year ago

Most people need some space after a coma. It takes a while for the brain to adjust to being awake again.

Collectives™ on Stack Overflow

String aggregation using JSON in SQL Server 2016

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related