convert JSON array of foreign keys to comma separated list of names (from other table's field)-CodePudding

Imagine that I have the following tables in a SQL Server database.

table name: products

 id  product_name 
 1   "Apple"
 2   "Banana"
 3   "Pear"
 4   "Peach"


 table name: users 

 id  user_name     likedProductsIds (JSON array of row ids for product table)
 1   "Joe"         "[1,2,3,4]"
 2   "Jose"        "[3,4]"
 3   "Kim"          NULL
 4   "Kelly"       "[4]"

I am looking for a query I could save as a SQL "view" to convert the JSON array "likedProductsId" into a comma seperated list of names of products. Such that the result would be:

 view name: report
 id  name     likedProductNames
 1  "Joe"     "Apple, Banana, Pear, Peach"
 2  "Jose"    "Pear, Peach"
 3  "Kim"      NULL,
 4  "Kelly"   "Peach"

I am a SQL newbee so I am have been really struggling with this due to the complexity of deserializing the JSON field and aggregating the results into a single comma separated string. I know some ingredients I will need such as OPENJSON to deserialize the IDs array and STRING_AGG to combine the product names into a single string, but I am lacking the glue to get them togther

 SELECT id, name FROM users
 LEFT JOIN what....

CodePudding user response：

Here is an option that uses OPENJSON in concert with an CROSS APPLY.

In order to maintain the sequence, we use ... within group (order by [key])

Example

Select U.id
      ,U.user_name
      ,P.likedProductNames
 From  users U
 Cross Apply (
                Select likedProductNames = string_agg([product_name],',') within group (order by [key])
                 from  OpenJSON([likedProductsIds]) A
                 Join  products B on A.value=B.id
             ) P

Results

id  user_name   likedProductNames
1   Joe         Apple,Banana,Pear,Peach
2   Jose        Pear,Peach
3   Kim         NULL
4   Kelly       Peach

UPDATE

Oddly enough, this may be a nudge more performant. (relative to the batch) : 30% vs the Cross Apply 37%

Select U.id
      ,U.user_name
      ,likedProductNames = (
                            Select string_agg([product_name],',') within group (order by [key])
                             From  OpenJSON([likedProductsIds]) A
                             Join  products B on A.value=B.id
                           )
 From  users U