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:

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')

 

SQL - Modify Array of JSON

 How to copy one array of JSON object into another array of JSON in SQL. Take this below sample copy array of empIds from one JSON into another JSON

DECLARE  @pArrayOfIntEmps nvarchar(max)='{ "empIds": [' + '1,2'+'] }'

--JSON_Query will return array value from json whereas JSON_VALUE return object value from json

--select JSON_QUERY(@pArrayOfIntEmps,'$.empIds') 

 

DECLARE @info NVARCHAR(max)='{"id":1,"abc":false,"empIds":[3,4] }'

SET @info=  JSON_MODIFY(ISNULL(@info,'{}'),'$.empIds,JSON_QUERY(@pArrayOfIntEmps,'$.empIds'))

select @info

 

--Output {"id":1,"abc":false,"empIds":[1,2] }, this empIds will be replaced by array of int

How to ignore property conditionally during JSON serialization

Consider i have class Employer, i want serialize this class to json string and need ignore some properties conditionally. For ex: in my example, i want to include address1 and address2 only if it has valid values

  public class Employer

    {

 public int id { get; set; }

        public string name { get; set; }

       

 public string ssn { get; set; }

        public string address1 { get; set; }

        public string address2 { get; set; }

   }

Declare some values

Employer employer = new Employer(){ … address1 = "some value", address2 = null };

Now serialize it for json string

var jsonstring = JsonConvert.SerializeObject(employer,

                       Newtonsoft.Json.Formatting.None,

                       new JsonSerializerSettings

                       {

                           

                       });

Here you will get all the properties.

Now lets see how to ignore the properties conditionally, you can choose either one of these options.

Option 1: Use ShouldSerialize property inside class itself like below. But you need add individual shouldSerialize property for each class property.

  public class Employer

    {

 public int id { get; set; }

        public string name { get; set; }

       

 public string ssn { get; set; }

        public string address1 { get; set; }

        public string address2 { get; set; }

        public bool ShouldSerializeaddress1()

        {

            // don't serialize if it is null or empty or add any your custom conditions

            return !string.IsNullOrEmpty(address1);

        }

        public bool ShouldSerializeaddress2()

        {

            // don't serialize if it is null or empty or add any your custom conditions

            return !string.IsNullOrEmpty(address2);

        }

   }

Option 2: Instead creating multiple ShouldSerialize property inside class, we can create ContractResolver and add it in Json serialization as below,

Create Resolver Class,

using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using System.Linq;
using System.Reflection;
...

  public class EmployerShouldSerializeContractResolver : DefaultContractResolver

    {

        public new static readonly EmployerShouldSerializeContractResolver Instance = new EmployerShouldSerializeContractResolver();

        protected override JsonProperty CreateProperty(MemberInfo member, MemberSerialization memberSerialization)

        {

            JsonProperty property = base.CreateProperty(member, memberSerialization);

            if (property.DeclaringType == typeof(Employer))

            {

                property.ShouldSerialize =

                    instance =>

                    {

                        //ignore default values

                        return instance.GetType().GetProperty(member.Name).GetValue(instance, null) != property.DefaultValue;

                    };

            }

            return property;

        }

    }

Include it in JSON serialization,

var jsonstring = JsonConvert.SerializeObject(employer,

                       Newtonsoft.Json.Formatting.None,

                       new JsonSerializerSettings

                       {

                           ContractResolver = new EmployerShouldSerializeContractResolver()

                       });