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, Nov 27 '19 at 09:11
@PanagiotisKanavos I was follwing his approch to generate that output. https://stackoverflow.com/a/42778050/764369 — Arooran, Nov 27 '19 at 09:17
This has nothing to do with *JSON*. You're asking how to aggregate strings in SQL Server 2016. [This answer](https://stackoverflow.com/a/3672860/134204) to that question shows the correct query, although it's *not* that different from the common XML method. No replacement needed, — Panagiotis Kanavos, Nov 27 '19 at 09: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, Nov 27 '19 at 09: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, Nov 27 '19 at 09:29
You need to cast the numeric field to text before using the concatenation query. Replacements won't work. The generated *JSON* string doesn't have a closing double quote — Panagiotis Kanavos, Nov 27 '19 at 09:50

Zhorov · Answer 1 · 2019-11-27T09:29:30.003

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

The OP is already trying to use a string aggregation technique based on JSON — Panagiotis Kanavos, Nov 27 '19 at 09:34

Panagiotis Kanavos · Accepted Answer · 2019-11-27T09:57:29.083

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

Very well explained. I have updated the question based on your request. — Arooran, Nov 27 '19 at 09:54
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. — Reversed Engineer, Mar 26 '21 at 09:40

score 1 · Answer 3 · answered Nov 27 '19 at 09:12

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 '19 at 09:12

Gosfly

1,240
1
8
14

1

Most people need some space after a [coma](https://medical-dictionary.thefreedictionary.com/coma). It takes a while for the brain to adjust to being awake again. – Reversed Engineer Mar 26 '21 at 08:24

String aggregation using JSON in SQL Server 2016

3 Answers3