mysql - json_unquote 和 extract 给出 null

Question

我正在使用这段 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;

这是我尝试从中提取的字段中的数据示例(列名称为“jurisdictions”):

    [{"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,

数组中的数组示例:

"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\"},

Answer 1

在子字段之前添加“[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;