Showing posts with label JSON Path. Show all posts
Showing posts with label JSON Path. Show all posts

Tuesday, February 20, 2024

SQL - Defined vs Dynamic table column values into JSON

 Convert Defined vs Dynamic table column values into JSON using SQL Syntax, using SQL Pivot and Subquery execution achieved to construct JSON,


IF OBJECT_ID('tempdb..#tempDefinedTable1') IS NOT NULL

       DROP TABLE #tempDefinedTable1

IF OBJECT_ID('tempdb..#tempDefinedTable2') IS NOT NULL

       DROP TABLE #tempDefinedTable2

IF OBJECT_ID('tempdb..#tempAllColumns') IS NOT NULL

       drop table #tempAllColumns

IF OBJECT_ID('tempdb..#tempMatchedColumns') IS NOT NULL

       drop table #tempMatchedColumns

IF OBJECT_ID('tempdb..#tempUnMatchedColumns') IS NOT NULL

       drop table #tempUnMatchedColumns

 


--create some defined column names as values

 create table #tempDefinedTable1 (

 definedcolnames varchar(max)

)

insert into #tempDefinedTable1

values ('col1'),('col2')

 

select * from #tempDefinedTable1

 

-- create table with defined columns + dynamic columns

create table #tempDefinedTable2 (

id int,

col1 varchar(max),

col2 varchar(max),

dynamicCol1 varchar(max),

dynamicCol2 varchar(max)

)

 

insert into #tempDefinedTable2

values (1, 'a1', 'b1', 'c1', 'd1'),(2, 'a2', 'b2', 'c2', 'd2')

 

select * from #tempDefinedTable2

  

--From the defined+dynamic column list, consolidate columns as values

Select C.* into #tempAllColumns

 From #tempDefinedTable2 A

 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)

 Cross Apply (

                Select ColumnName  = a.value('local-name(.)','varchar(100)')

                      ,ColumnValue = a.value('.','varchar(max)')

                                     ,Id

                 From  B.XMLData.nodes('/row')  as C1(n)

                 Cross Apply C1.n.nodes('./@*') as C2(a)

                 Where a.value('local-name(.)','varchar(100)') not in ('ID','ExcludeOtherCol')

             ) C

 

select Id, ColumnName, ColumnValue from #tempAllColumns

 

--now preparing sql sub query to list defined columns in root level, dynamic columns as nested object

DECLARE @Columns as VARCHAR(MAX)

 

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME([ColumnName])

FROM

    (SELECT DISTINCT ColumnName FROM #tempAllColumns st

       JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

       WHERE tt.definedcolnames is not null) AS B

ORDER BY B.ColumnName

 

select @Columns as DefinedColumns

 

 

DECLARE @DynamicColumns as VARCHAR(MAX)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ','') + QUOTENAME([ColumnName])

FROM

    (SELECT DISTINCT ColumnName FROM #tempAllColumns st

       LEFT JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

       WHERE tt.definedcolnames is null) AS B

ORDER BY B.ColumnName

select @DynamicColumns  as UndefinedColumns

 

DECLARE @SQL as VARCHAR(MAX)

SET @SQL = 'SELECT Id,' + @Columns + ',

       (SELECT PR1.Id,' + @DynamicColumns + ' FROM (select st1.Id, st1.[ColumnName], st1.ColumnValue from #tempAllColumns st1

        LEFT JOIN #tempDefinedTable1 tt1 on tt1.definedcolnames = st1.ColumnName

        WHERE tt1.definedcolnames is null) as PD2

        PIVOT (

          max(ColumnValue)

          FOR [ColumnName] IN (' + @DynamicColumns + ')

       ) AS PR1

       WHERE PR1.Id = PR.Id

       ORDER BY Id

       FOR JSON PATH, INCLUDE_NULL_VALUES) as ExtraFields

FROM

(

 select Id,[ColumnName],ColumnValue from #tempAllColumns st

 JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

 WHERE tt.definedcolnames is not null

) as PD

PIVOT

(

   max(ColumnValue)

   FOR [ColumnName] IN (' + @Columns + ')

) AS PR

ORDER BY Id

FOR JSON PATH, INCLUDE_NULL_VALUES'

 

EXEC(@SQL);

 

Columns:


Output:





Monday, April 25, 2022

JSON Path to use in Angular Projects

How to use JSON Path to query any JSON object.

For example, consider below simple JSON, if you want to query JSON we can use simply query as this.obj.name or this.obj.id to access to the JSON

this.obj ={

   id: 1,

   "name": "balaji"

}

For array object we can use index, for ex: this.objData[0].id. For more JSON path to find we can refer https://jsonpathfinder.com/

this.objData = {

        data: [

  {

    "id": 1,

    "name": "Balajiprasad",

    "age": 18,

              "address": {

                      "state": "TN",

                       "country": "India"

                } 

 

  },

  {

    "id": 2,

    "name": "prasad",

    "age": 28,

     "address": {

                      "state": "Tx",

                       "country": "US"

                } 

  }

]}


In angular project, if we need to use JSON path dynamically then we can refer the jsonpath library Ref: https://www.npmjs.com/package/jsonpath

Steps to use,

1. Add "json-path" in package.json file under dependencies

    "dependencies": {

"jsonpath": "1.1.1"

}


2. Add reference of "json-path" directly in component. No need to specify in App.module

    import * as jp from 'jsonpath'

3. Use the JSON path in code , need to use $.. (double dots)

   let state = jp.query(this.objData, '$..data[0].address.state')