Sql-server – How to get json data along with select columns in Sql

jsonsql server

I'm trying to get the table data as json along with columns on the same table which is joining another table.

COUNTRY

COUNTRY_ID  |COUNTRY_CD
1           |NL
2           |FR
3           |PL

TEST_TEMP_TABLE

Id  |Test_Cd    |Test_Nm
1   |CodeOne    |Code one
2   |CodeTwo    |Code two

TEST_TEMP_CHILD_TABLE

Id  |Test_Temp_Table_Id |Test_Temp_Child_Val    |Test_Country_Id
1   |1                  |1.10000                |1
2   |1                  |1.20000                |1
3   |1                  |1.30000                |1
4   |1                  |1.40000                |2
5   |1                  |1.50000                |2
6   |2                  |1.60000                |3
7   |2                  |1.70000                |3

So, my expected output should be,

PartitionKey |RowKey        |Country_Cd |Test_Temp_Id   |Country_Id |jsCol
FR           |FR|CodeOne    |FR         |1              |2          |[{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.40000},{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.50000}]
NL           |NL|CodeOne    |NL         |1              |1          |[{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.10000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.20000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.30000}]
PL           |PL|CodeTwo    |PL         |2              |3          |[{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.60000},{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.70000}]

Actual result

PartitionKey  |RowKey     |Country_Cd   |Test_Temp_Id   |Country_Id |jsCol
FR            |FR|CodeOne |FR           |1              |2          |[{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.10000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.20000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.30000},{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.40000},{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.50000},{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.60000},{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.70000}]
NL            |NL|CodeOne |NL            |1             |1          |[{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.10000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.20000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.30000},{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.40000},{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.50000},{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.60000},{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.70000}]
PL            |PL|CodeTwo |PL            |2             |3          |[{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.10000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.20000},{"COUNTRY_CD":"NL","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.30000},{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.40000},{"COUNTRY_CD":"FR","Test_Temp_Table_Id":1,"Test_Temp_Child_Val":1.50000},{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.60000},{"COUNTRY_CD":"PL","Test_Temp_Table_Id":2,"Test_Temp_Child_Val":1.70000}]

I've tried below query, but i'm not getting expected json column result,

;with cte as(select distinct
cou.Country_Cd as PartitionKey
,cou.Country_Cd+'|'+ttt.Test_Cd as RowKey
,cou.Country_Cd
,ttt.Id as Test_Temp_Id
,cou.Country_Id
from Test_Temp_Table ttt
inner join Test_Temp_Child_Table ttcd on ttcd.Test_Temp_Table_Id = ttt.Id
inner join Country cou on cou.country_id = ttcd.Test_Country_Id)
select c1.*, jsCol = (
select c1.COUNTRY_CD, ttcd.Test_Temp_Table_Id, ttcd.Test_Temp_Child_Val from cte c1
inner join TEST_TEMP_CHILD_TABLE ttcd on ttcd.Test_Temp_Table_Id = c1.Test_Temp_Id and ttcd.Test_Country_Id = c1.COUNTRY_ID
for json path
)
from cte c1

kindly suggest me

Best Answer

At this point in your query you should only be referring to the aliases of the CTE, that is c1. The other aliases are no longer visible

select c1.*, jsCol = ( select c1.COUNTRY_CD, ttcd.Test_Temp_Table_Id, ttcd.Test_Temp_Child_Val from cte c1 inner join TEST_TEMP_CHILD_TABLE ttcd on ttcd.Test_Temp_Table_Id = c1.Test_Temp_Id and ttcd.Test_Country_Id = c1.COUNTRY_ID for json path ) from cte c1