解析字符串SQL Server，Ruby，Python？

发布于 2025-01-19 13:24:35 字数 316 浏览 0 评论 0原文

我正在尝试解析这种数据的刺痛...它是来自API获取的一个领域...

{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}

看起来很熟悉。就像Ruby中的JSON HASH一样……

什么是解析此方法的最佳方法。我将数据放在SQL Server表中，因此更可取TSQL。但是，如果更好的话，我也会考虑Ruby或Python。

原文

I'm trying to parse this sting of data...its an field coming from an API GET...

{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}

It looks familiar. Like a JSON hash in ruby...

What would be the best method to parse this. I have the data in a SQL Server table so TSQL preferable. But I would also consider Ruby or Python if it was better..

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌化 2025-01-26 13:24:35

您可以尝试将存储的API数据转换为JSON。

示例数据：

SELECT *
INTO Data
FROM (VALUES
   ('{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}'),
   ('Invalid JSON data')
) v (ApiData)

语句：

SELECT t.ApiData, j.*
FROM (
   SELECT 
      ApiData,
      JsonData = CASE
         WHEN ISJSON(REPLACE(ApiData, '=>', ':')) = 1 THEN REPLACE(ApiData, '=>', ':')
         ELSE NULL
      END   
   FROM Data
) t  
OUTER APPLY OPENJSON(t.JsonData) j

结果：

apidata	键值	类型	{“ displayValue” =＆gt;“ lna1234”，“ ismulti” =＆gt; true，“ textValue” =＆gt;“ pna64582
”，“ pny64582”，“ type” =＆gt; “ value” =＆gt; [srp_zfeedr88n']}}	displayValue	lna1234	1
{“ displayValue” =＆gt;“ lna1234”，“ ismulti” =＆gt; true，true，“ textValue，” textValue，“ textValue” =＆gt; “ entity_link”，“ value” =＆gt; [“ srp_zfeedr88n”]}	ismulti	true	3
{“ displayValue” =＆gt;“ lna1234”，“ ismulti” =＆gt; trie，trie，true，trie，triev; “ =＆gt;” entity_link”，“ value” =＆gt; [srp_zfeedr88n']}	textValue	pna64582	1
{“ displayValue” =＆gt;“ lna1234” “，” type“ =＆gt;” entity_link“，“ value” =＆gt; [srp_zfeedr88n']}	type	entity_link	1
{“ displayValue” =＆gt;“ lna1234”，“ ＆gt;“ pna64582”，“ type” =＆gt;“ entity_link”，“ value” =＆gt; [srp_zfeedr88n'}	value	[srp_zfeedr88n']	4
无效的JSON DATA

You may try to transform and parse the stored API data as JSON.

Sample data:

SELECT *
INTO Data
FROM (VALUES
   ('{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}'),
   ('Invalid JSON data')
) v (ApiData)

Statement:

SELECT t.ApiData, j.*
FROM (
   SELECT 
      ApiData,
      JsonData = CASE
         WHEN ISJSON(REPLACE(ApiData, '=>', ':')) = 1 THEN REPLACE(ApiData, '=>', ':')
         ELSE NULL
      END   
   FROM Data
) t  
OUTER APPLY OPENJSON(t.JsonData) j

Result:

ApiData	key	value	type
{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}	displayValue	LNA1234	1
{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}	isMulti	true	3
{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}	textValue	PNA64582	1
{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}	type	entity_link	1
{"displayValue"=>"LNA1234", "isMulti"=>true, "textValue"=>"PNA64582", "type"=>"entity_link", "value"=>["srp_zfeedr88N"]}	value	["srp_zfeedr88N"]	4
Invalid JSON data