SQL Server JSON array foreign key values using join

Question

Below is schema of the data stored in SQL Server 2016 Database

+------------+-------------+
| CountryKey | CountryName |
+------------+-------------+
|          1 | USA         |
|          2 | Japan       |
|          3 | China       |
|          4 | UK          |
|          5 | Australia   |
+------------+-------------+

And in the Store table the information is saved as JSON

+-------+-----------+
| Store | Countries |
+-------+-----------+
| A     | [1,2]     |
| B     | [1,3]     |
| C     | [1,3,4]   |
| D     | [4,5]     |
| E     | [1,5]     |
+-------+-----------+

This design is working fine when I want to filter data, for example I'm able to use OPEN_JSON to filter the stores based on CountryKey

But after filter - the requirement is to show Country Name instead of Country Key as shown below -

Desired Output

+-------+----------------+
| Store | CountriesName  |
+-------+----------------+
| A     | USA, Japan     |
| B     | USA, China     |
| C     | USA, China, UK |
| D     | UK, Asutralia  |
| E     | USA, Australia |
+-------+----------------+

My Question is can this be achieved with SQL Query or need to process data with server side language e.g C#. If yes syntax and references will help.

Personally, I would suggest fixing the design to a normalised approach. Not only do you need to split the data here, you then want to rebuild it into a comma separated list. — Thom A
– Thom A ♦, Commented Mar 12, 2020 at 10:09
But the advantage of using JSON in SQL server 2016 will be gone. I need this tabular information only for report. — Gaurav P
– Gaurav P, Commented Mar 12, 2020 at 10:43
Storing relational data is not what you're meant to be using JSON data in SQL server for though. That should be in normalised tables. — Thom A
– Thom A ♦, Commented Mar 12, 2020 at 10:45

Zhorov · Accepted Answer · 2020-03-12 10:48:50Z

2

One possible solution is to parse the country code with OPENJSON(), use appropriate join and aggregate the country names. For SQL Server 2016 you need to use FOR XML PATH for string aggregation:

Tables:

CREATE TABLE Country (
   CountryKey int,
   CountryName nvarchar(100)
)
INSERT INTO Country 
   (CountryKey, CountryName)
VALUES
   (1, N'USA'),
   (2, N'Japan'),   
   (3, N'China'),   
   (4, N'UK'),      
   (5, N'Australia')
CREATE TABLE Store (
   Store nvarchar(1),
   Countries nvarchar(max)
)
INSERT INTO Store
   (Store, Countries)
VALUES   
   (N'A', N'[1,2]'),
   (N'B', N'[1,3]'),
   (N'C', N'[1,3,4]'),
   (N'D', N'[4,5]'),
   (N'E', N'[1,5]')

Statement:

SELECT s.Store, STUFF(t.Country, 1, 2, N'') AS Country
FROM Store s
CROSS APPLY (
   SELECT ', ' + c.CountryName
   FROM OPENJSON(s.Countries) WITH (CountryKey int '$') j
   LEFT JOIN Country c ON c.CountryKey = j.CountryKey
   FOR XML PATH('')
) t (Country)

Result:

Store   Country
A       USA, Japan
B       USA, China
C       USA, China, UK
D       UK, Australia
E       USA, Australia

If you can upgrade to SQL Server 2017, string aggregation can be done using STRING_AGG():

SELECT s.Store, t.Country
FROM Store s
CROSS APPLY (
   SELECT STRING_AGG(c.CountryName, N', ')
   FROM OPENJSON(s.Countries) WITH (CountryKey int '$') j
   LEFT JOIN Country c ON c.CountryKey = j.CountryKey
) t (Country)

edited Mar 12, 2020 at 10:48

answered Mar 12, 2020 at 10:30

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

1 Comment

Gaurav P Over a year ago

Great this is exactly the same output, I was looking for

Collectives™ on Stack Overflow

SQL Server JSON array foreign key values using join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related