How to get json data along with select columns in Sql

Question

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

Answer 1

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

• cgsabari: Thank you @Stephen for your comment. Your query also result same my actual result.
• Stephen Morris - Mo64: "My query" is cut and paste from yours - you need to change the column aliases to c1 as this is the only alias which is valid in this scope (the other aliases are valid within the definition of the CTE but not outside of that definition).
• cgsabari: Thank you @Stephen :-). Just noticed. its working fine. now.
• Stephen Morris - Mo64: could you accept the answer then ?
• cgsabari: yes accepted. :)