SQL Server 2016 Json parsing super man...

[loveindia]

If you are using Json and SQL Server 2016, its an awesome combo man... I am using it hands on currently and super experience so far... just a quick example for newbies interested in this...

	DECLARE @Json NVARCHAR(MAX)
	SET @Json = '{"requests": [
	    {
	      "id": 0,
	      "data": [
	        {
	          "id": 0,
	          "field": "customerAddress",
	          "stringValue": "2301 Perf. Drive"
	        }
	      ],
	      "where": [
	        {
	          "id": 0,
	          "field": "customerid",
	          "intValue": 1
	        },
	        {
	          "id": 1,
	          "field": "isActive",
	          "stringValue": "1"
	        }
	      ],
	      "table": "custAddress"
	    },
	    {
	      "id": 1,
	      "data": [
	        {
	          "id": 0,
	          "field": "customerAddress",
	          "stringValue": "111 West Irving"
	        }
	      ],
	      "where": [
	        {
	          "id": 0,
	          "field": "customerid",
	          "intValue": 2
	        },
	        {
	          "id": 1,
	          "field": "isActive",
	          "stringValue": "1"
	        }
	      ],
	      "table": "custAddress"
	    }
	    ]
	}    '
	;WITH CTE AS
	(
	SELECT *
	FROM OpenJson(@Json, '$.requests')
	)
	SELECT a.[key]
	    , JSON_VALUE(b.[value], '$.field') AS update_field
	    , JSON_VALUE(b.[value], '$.stringValue') AS update_value
	    , JSON_VALUE(c.[value], '$.field') AS where_field
	    , ISNULL(JSON_VALUE(c.[value], '$.stringValue'), JSON_VALUE(c.[value], '$.intValue')) AS where_value
	FROM CTE a
	CROSS APPLY OpenJson(a.[value], '$.data') b
	CROSS APPLY OpenJson(a.[value], '$.where') c
	

[loveindia]

[Kontekurradu]

[agora]

k

[mtkr]

wht is Json??

[loveindia]

Just now, mtkr said:

wht is Json??

non - relational representation of data man... similar to xml but more advanced and easy to understand.. 

[Kontekurradu]

Just now, loveindia said:

non - relational representation of data man... similar to xml but more advanced and easy to understand.. 

Ankulu naaku SQL nerpisthava ? i am serious 

[loveindia]

Just now, Kontekurradu said:

Ankulu naaku SQL nerpisthava ? i am serious 

naaku vachindi nerpinchadaaniki I am always ready man... 

[Kontekurradu]

Just now, loveindia said:

naaku vachindi nerpinchadaaniki I am always ready man... 

Thanks will PM 

[yomama]

Uncle endidi what's this parsing

[SwamyRaRa]

Ok enjoy 

[loveindia]

3 minutes ago, yomama said:

Uncle endidi what's this parsing

parsing antey analyzing the data, so JSON to sql result set techukoni we will analyze man...

[sqladdict]

Json with ms sql enduku..licensing ki money waste....mongodb bettrr....app prgramming open source vundu mallu ..sql enduku licnese ki money spend cheyyali

[loveindia]

Just now, sqladdict said:

Json with ms sql enduku..licensing ki money waste....mongodb bettrr....app prgramming open source vundu mallu ..sql enduku licnese ki money spend cheyyali

lol, company ki license konatam valla tax tagguddi and moreover flexibility to use their data will increase man... mongodb enti ye tech lo aina cheyochu whatever you want... but business will lose its flexibility in order to make self servicing BI solutions... 

[yomama]

4 minutes ago, loveindia said:

parsing antey analyzing the data, so JSON to sql result set techukoni we will analyze man...

Asal Json em doing uncle analyze ante how? I see customer address testunav but did get what Ur doing??