0

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

0

1 Answer 1

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
5
  • Thank you @Stephen for your comment. Your query also result same my actual result.
    – cgsabari
    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). May 18, 2021 at 11:28
  • Thank you @Stephen :-). Just noticed. its working fine. now.
    – cgsabari
    May 18, 2021 at 11:38
  • could you accept the answer then ? May 18, 2021 at 11:39
  • yes accepted. :)
    – cgsabari
    May 18, 2021 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.