Tukukkk

I have 2 tables as below and I am currently writing a function that returns a JSON based on these tables. Need help in querying this.

 TABLE1

 Id    ReqMode    RespMode    Count    TabName   

 1001  R          T           5000     TAB3

 1002  R          Y           10000    TAB4

 1003  R          T           3000     TAB6

 1004  R          T           5000     TAB5

 1005  R          Y           6000     TAB2





 TABLE2

 TabName  CMeth  CDate  UMeth  UDate DMeth  DDate  ParentTab  

 TAB1     F      A1     F      L1    C

 TAB2     P             C            C             TAB1

 TAB3     P             C            C             TAB2

 TAB4     F      B5     C            C                

 TAB5     P             C            C             TAB4

 TAB6     C             C            C

JSON Format :

{  

   "d":{  

      "Id":"1001",

      "ReqMode":"R",

      "RespMode":"T",

      "Count":5000,

      "TabName":"TAB3",

      "DELTA":[  

         {  

            "Tabname":"TAB3",

            "CMeth":"P",

            "CDate":"",

            "UMeth":"C",

            "UDate":"",

            "DMeth":"C",

            "DDate":"",

            "ParentTab":"TAB2"

         },

         {  

            "Tabname":"TAB2",

            "CMeth":"P",

            "CDate":"",

            "UMeth":"C",

            "UDate":"",

            "DMeth":"C",

            "DDate":"",

            "ParentTab":"TAB1"

         },

         {  

            "Tabname":"TAB1",

            "CMeth":"F",

            "CDate":"A1",

            "UMeth":"F",

            "UDate":"L1",

            "DMeth":"",

            "DDate":"C",

            "ParentTab":""

         }

      ],

      "MDATA":[ ]

   }

}

Explanation :

If I am requesting for 'TAB3' , then I need to pull a record from TABLE1 where TabName is TAB3. Based on this table name, I check for its values in TABLE2.

My methods have values P(Parent),C(Change),F(Field)

The logic behind getting 3 rows here in my DELTA part of JSON is :

For TabName TAB3, if P is present in any of the Methods in TABLE2,then pick the ParentTab value and comapare it against TabName in TABLE2 and get its details. This is a recursive loop until we come across C or F in any of the Methods.

I am currently lost in how to create a recursive loop based on the values. My query so far :

select Id AS 'd.Id', ReqMode AS 'd.ReqMode' ,RespMode AS 'd.RespMode',Count AS 'd.Count',TabName AS 'd.TabName'

,JSON_QUERY('') 'd.DELTA',JSON_QUERY('') 'd.MDATA'

FROM TABLE1 

FOR JSON PATH

Need help in creating DELTA part of my JSON which will depend on the TabName I pass as an input to the function.

Thanks in advance.

This answer is a little late, but this should give you what you're looking for. It is important to note recursive cte's have a default limit of 100, but you can use the MAXRECURSION option to configure a reasonable limit for your situation.

declare @id int = 1001

declare @tab varchar(100) = (select tabName from Table1 where id = @id);



with delta as (

    select tabname,cmeth,isnull(cdate,'') as cdate

      ,umeth,isnull(udate,'') as udate

      ,dmeth,isnull(ddate,'') as ddate

      ,isnull(parenttab,'') as parenttab 

    from dbo.Table2 

    where tabname = @tab

    union all

    select t.tabname,t.cmeth,isnull(t.cdate,'') as cdate

      ,t.umeth,isnull(t.udate,'') as udate

      ,t.dmeth,isnull(t.ddate,'') as ddate

      ,isnull(t.parenttab,'') as parenttab  

    from dbo.Table2 t 

      inner join delta d on t.tabname = d.parenttab

)

SELECT ID as 'd.id'

    ,ReqMode as 'd.reqmode'

    ,RespMode as 'd.respmode'

    ,[Count] as 'd.count'

    ,TabName as 'd.tabname'

    ,json_query((select distinct * from delta for json path)) as delta

    ,json_query('') as mdata

FROM dbo.Table1 T1

WHERE ID = @id

FOR JSON PATH;

go