如何将json格式转换为SQL [英] How to covert json format into SQL

问题描述

我有一个字符串@data它包含这个值

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"
}'

现在我想转换成列





喜欢



dcrno，doctorcode，salable，pob，note，time

喜欢这个..是吧有可能转换这个请帮我这样做



我尝试过：

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:

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

推荐答案

您是否必须在SQL中执行此操作？ - 它会非常混乱 - 我想你可以写一个存储过程来做它



有一个明显的问题 - 可售项目 - 一个{dcrno，doctorcode，salable， pob，note，time}'记录'缺少一个更好的单词会有多个可销售的项目，似乎对应于样品/促销/礼品项目 - 你需要弄清楚如何处理它们



处理'salable'的一种可能方法，将商品代码和数量编码成单个字符串可能看起来像



场地：价值

============

dcrno：20

doctorcode：di001

salable：it001：10，it002：2，it003：7

pob：788

注意：rajvalue

时间：13 ：78：90



然后你需要逻辑来编码和解码'salable' - '明显'的方法是放置可销售的物品 - 物品代码和数量到数据库中的一个单独的表中，带有一个返回'master'记录的链接



如果我在任何体面的HLL中进行，我会将JSON解析为（一组）POCO并从那里开始 - 我不确定这个标签'sample / promo / gift'是严格合法的btw

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

在SQL Server 2016中，您有从JSON文本（JSON_VALUE，OPENJSON等）读取值的函数。这些函数将在Azure中提供Sql数据库很快。

如果您有一些旧版本的SQL Server，最好的方法是使用一些解析JSON的CLR程序集，如JSONSelect。

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.

这篇关于如何将json格式转换为SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！