Return JSON List of Values in SQL Server 2016 Using Data from Two Columns

sql serversql-server-2016

I have a database with 2 columns

A     B
--    --
X     1995
Y     2005
C     1962
D     2003

I'm trying to create a SQL statement that will take a string of comma delimited values and return a json list of values in B where any value in the string is in A

so if the comma delimited string was 'X,C' the json list would be [1995,1962]

I've been using json path to try this, but I can't get it exactly like I want it and I've been spinning my wheels for too long

This is what I've tried:

Select mt.B as json_list_b_values
 From  [dbo].[myTable] mt
Where  mt.A in (Select value From String_Split('X,C', ',')) for json path

This is the ouput:

[ {"json_list_b_values":"1995"}, {"json_list_b_values":"1962"} ]

Best Answer

If you are able to use FOR XML PATH('')

SELECT  DISTINCT
 QUOTENAME((STUFF(
    (
      SELECT ',' + try_convert(varchar(4),mt.B)
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR XML PATH('')
    ), 1, 1, '')));

If not, you could do a lot of REPLACE()ments

    SELECT  DISTINCT
 QUOTENAME(REPLACE(REPLACE(REPLACE(REPLACE((STUFF(
    (
      SELECT  mt.B as '}'
      FROM [dbo].[myTable] mt
      WHERE mt.a in ('X','C')
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ), 1, 1, '')),'"',''),'}',''),'{',''),':',''))

Result for both:

(No column name)
[1995,1962]
Related Question