mysql - json_unquote 和提取给出空值
问题描述
我正在使用这段 mysql 代码从数组中提取数据,但是当我运行它时我得到了空值。我的脚本没有解决双引号问题吗?任何帮助表示赞赏。这是我尝试运行的脚本示例:
select
courtguid,
officialname,
json_unquote(json_extract(jurisdictions, '$.lang')) as jx_lang,
json_unquote(json_extract(jurisdictions, '$.start_Date')) as jx_start_date,
json_unquote(json_extract(jurisdictions, '$.governmentLevelType')) as jx_gov_level_type
from courts_full;
这是我试图从中提取的字段中的一些数据示例(列名是“管辖区”):
[{"lang": "en", "startDate": "2012-04-10", "geopoliticalBody": [], "governmentLevelType": "State", "governmentLevelTypeGuid": "urn:propertyValueItem:0D85EC301A4A461AB8A71270D40E9FE5", "jurisdictionLevel": "Intermediate Appeals", "codedRepresentation": {"codedGeographicTerritoryGuid": "urn:propertyValueItem:C906FA55EE8D4358BE3FEF2F0BBA7D31", "codedGeographicSite": "KNO", "codedGeographicSiteGuid": "urn:propertyValueItem:046C1D8B05AB4A88BE767D39AE15DE5E", "codedSubjectMatterTypeGuid": "urn:propertyValueItem:BB019AD0664D4C1A85152C9C307C7720", "codedSubjectMatterType": "APL-CRM", "codedGeographicTerritory": "EGD", "codedGovernmentSystem": "US-TN-J", "codedGovernmentSystemGuid": "urn:propertyValueItem:1F1D25A1D9A24D699499B678DA2CBEAF"}, "appellateJurisdictions": [], "reportedDate": "2012-04-10", "jurisdictionLevelGuid": "urn:propertyValueItem:53B0C6AF9A68462A98D7B49F3A9014F0", "isStartDateComputed": true}]
跟进:这段脚本也无法获取数组中的数组
json_unquote(json_extract(jurisdictions, '$.codedRepresentation.codedGeographicTerritory')) as coded_geo_territory,
数组 w/in 数组示例:
"jurisdictions" : \"codedRepresentation\": {\"codedGeographicTerritoryGuid\": \"urn:propertyValueItem:C906FA55EE8D4358BE3FEF2F0BBA7D31\", \"codedGeographicSite\": \"KNO\", \"codedGeographicSiteGuid\": \"urn:propertyValueItem:046C1D8B05AB4A88BE767D39AE15DE5E\", \"codedSubjectMatterTypeGuid\": \"urn:propertyValueItem:BB019AD0664D4C1A85152C9C307C7720\", \"codedSubjectMatterType\": \"APL-CRM\", \"codedGeographicTerritory\": \"EGD\", \"codedGovernmentSystem\": \"US-TN-J\", \"codedGovernmentSystemGuid\": \"urn:propertyValueItem:1F1D25A1D9A24D699499B678DA2CBEAF\"},
解决方案
在我的子字段之前添加一个 '[0]' 给了我 a 正在寻找的内容:
select
courtguid,
officialname,
json_unquote(json_extract(jurisdictions, '$[0].lang')) as jx_lang,
json_unquote(json_extract(jurisdictions, '$[0].start_Date')) as jx_start_date,
json_unquote(json_extract(jurisdictions, '$[0].governmentLevelType')) as jx_gov_level_type
from courts_full;
推荐阅读
- vlc - 如何解决我的 vlc 媒体问题,它显示 MRL 文件格式问题
- vba - 没有ID的VBA单击按钮
- python - Python - Find Coordinates Relative to Screen
- concurrency - 如何解决 ClickHouse 死锁?
- javascript - Navigation Menu CSS not linking
- unity3d - Unity 中的主摄像头错误:“此摄像头使用的渲染器不支持摄像头堆叠。只有基本摄像头会渲染。”
- swift - 链接显示为红色而不是蓝色?
- json - Serialize a Map
> using gson Grails view - javascript - React Reduct - Select Onchange get method request
- gnuplot - 在 Gnuplot 中使用 splot;如何为一列执行“w 行”,基于另一列执行“lc 调色板”