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

Stephen Morris - Mo64 · Accepted Answer · 2021-05-18 09:50:13Z

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

answered May 18, 2021 at 9:50

Stephen Morris - Mo64

4,6741 gold badge10 silver badges18 bronze badges

Thank you @Stephen for your comment. Your query also result same my actual result.

cgsabari
– cgsabari

2021-05-18 10:11:49 +00:00
Commented May 18, 2021 at 10:11
"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).

Stephen Morris - Mo64
– Stephen Morris - Mo64

2021-05-18 11:28:31 +00:00
Commented May 18, 2021 at 11:28
Thank you @Stephen :-). Just noticed. its working fine. now.

cgsabari
– cgsabari

2021-05-18 11:38:00 +00:00
Commented May 18, 2021 at 11:38
could you accept the answer then ?

Stephen Morris - Mo64
– Stephen Morris - Mo64

2021-05-18 11:39:34 +00:00
Commented May 18, 2021 at 11:39
yes accepted. :)

cgsabari
– cgsabari

2021-05-18 12:58:45 +00:00
Commented May 18, 2021 at 12:58

Add a comment |

Stack Exchange Network

How to get json data along with select columns in Sql

1 Answer 1

Your Answer

Hot Network Questions

How to get json data along with select columns in Sql

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions