How to covert json format into SQL

Question

0.00/5 (No votes)

See more:

I have one string @data it is contained this value

@data=
{
	"dcrno": "20",
	"doctorcode": "di001",
	"salableitems": 
	[
	{
	"itemcode":"it0001"
	},
	{
	"itemcode":"it0003"
	},
	{
	"itemcode":"it0002"
	}
	],
	
	"sample/promo/gift": 
	[
	
	    {
	        "itemcode": "it001",
	        "itemqty": 10
	    },

	    {
	        "item code": "it002",
	        "itemqty": 2
	    },

	    {
	        "item code": "it009",
	        "itemqty": 7
	    }
	    
	],
	"pob": "788",
	"not": "rajvalue",
	"time": "13:78:90"
}

like

@data='@data=
{
	"dcrno": "20",
	"doctorcode": "di001",
	"salableitems": 
	[
	{
	"itemcode":"it0001"
	},
	{
	"itemcode":"it0003"
	},
	{
	"itemcode":"it0002"
	}
	],
	
	"sample/promo/gift": 
	[
	
	    {
	        "itemcode": "it001",
	        "itemqty": 10
	    },

	    {
	        "item code": "it002",
	        "itemqty": 2
	    },

	    {
	        "item code": "it009",
	        "itemqty": 7
	    }
	    
	],
	"pob": "788",
	"not": "rajvalue",
	"time": "13:78:90"
}'

now i want to convert into columns

like

dcrno,doctorcode,salable,pob,note,time
like this ..is it possible to convert likt this please help me to do that

What I have tried:

SQL

set @data=substring(@data,charindex(':',@data)),len(@data)-1)

Posted 1-Mar-16 18:36pm

Member 11337367

Updated 5-Apr-16 11:59am

Patrice T

v4

Add a Solution

Comments

dan!sh 2-Mar-16 0:44am

Does not really make much sense. You do not post JSON to your database. What exactly are you looking for? Also, get rid of this massive JSON message just share the structure.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2016-03-01T19:05:00

Do you HAVE to do this in SQL ? - it would be very messy - I guess you could write a stored procedure to do it

There's one glaring issue - saleable items - one {dcrno,doctorcode,salable,pob,note,time} 'record' for lack of a better word would have multiple salable items that seem to correspond to "sample/promo/gift" items - you you'd need to figure out how to handle them

One possible way of handling 'salable', encoding the item code and the quantity into a single string might look like

Field : Value
============
dcrno : 20
doctorcode : di001
salable : it001:10,it002:2,it003:7
pob : 788
note : rajvalue
time : 13:78:90

but then you need logic to encode and decode 'salable' - the 'obvious' way is to put salable items - the item code and the quantity into a separate table in the database, with a link back to the 'master' record

If I were doing it in any decent HLL, I'd parse the JSON to a (set of) POCO's and go from there - I'm not sure wether this tag 'sample/promo/gift' is strictly legal btw

Jovan Popovic(MSFT) · Answer 2 · 2016-03-01T21:58:00

Solution 2

In SQL Server 2016 you have functions that read values from JSON text (JSON_VALUE, OPENJSON, etc.) These will be available in Azure Sql Database soon.
If you have some older version of SQL Server, the best way is to use some CLR assembly that parses JSON such as JSONSelect.

Posted 1-Mar-16 21:58pm

Jovan Popovic(MSFT)

Comments

Member 11337367 2-Mar-16 4:13am

I have 2012 sql server any fuction is there in sql 2012.?