title: SQL Server JSON array foreign key values using join
body: <p>Below is schema of the data stored in SQL Server 2016 Database</p>
  
  <pre><code>+------------+-------------+
  | CountryKey | CountryName |
  +------------+-------------+
  |          1 | USA         |
  |          2 | Japan       |
  |          3 | China       |
  |          4 | UK          |
  |          5 | Australia   |
  +------------+-------------+
  </code></pre>
  
  <p>And in the Store table the information is saved as JSON </p>
  
  <pre><code>+-------+-----------+
  | Store | Countries |
  +-------+-----------+
  | A     | [1,2]     |
  | B     | [1,3]     |
  | C     | [1,3,4]   |
  | D     | [4,5]     |
  | E     | [1,5]     |
  +-------+-----------+
  </code></pre>
  
  <p>This design is working fine when I want to filter data, for example I'm able to use <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15" rel="nofollow noreferrer">OPEN_JSON</a> to filter the stores based on <code>CountryKey</code> </p>
  
  <p>But after filter - the requirement is to show <code>Country Name</code> instead of Country Key as shown below - </p>
  
  <p><strong>Desired Output</strong></p>
  
  <pre><code>+-------+----------------+
  | Store | CountriesName  |
  +-------+----------------+
  | A     | USA, Japan     |
  | B     | USA, China     |
  | C     | USA, China, UK |
  | D     | UK, Asutralia  |
  | E     | USA, Australia |
  +-------+----------------+
  </code></pre>
  
  <p><strong>My Question</strong> is can this be achieved with <code>SQL Query</code> or need to process data with server side language e.g <code>C#</code>.
  If yes syntax and references will help.</p>