CODEWITHSUNDEEP
sqljsonsql-servert-sqlsql-server-2016
Arooran
Arooran

Reputation: 637

String aggregation using JSON in SQL Server 2016

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.

Upvotes: 0

Views: 2080

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131553

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

Upvotes: 2

Zhorov
Zhorov

Reputation: 29983

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

Upvotes: 2

Gosfly
Gosfly

Reputation: 1300

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.

Upvotes: 1

Related Questions