0

I'm using this bit of mysql code to pull out pieces of data from an array, but I'm getting back nulls when I run it. Is it a double quote issue that my script is not addressing? Any help is appreciated. Here is the a sample of the script that I'm trying to run:

    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;

Here is a sample bit of data from the field that I'm trying to extract from (column name is '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}]

Follow up: this bit of script didn't work either to get at an array within an array

json_unquote(json_extract(jurisdictions, '$.codedRepresentation.codedGeographicTerritory')) as coded_geo_territory,

array w/in array example:

"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\"},
nkramer52
  • 53
  • 6

1 Answers1

0

Adding in a '[0]' before my sub-field gave me what a was looking for:

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;
nkramer52
  • 53
  • 6