How to parse a string json type field

Elastic Stack Elasticsearch
1

hi, i have a text type filed(index: false) -- detail , how to parse it into a geo_shape field?

{
  "took" : 23,
  "timed_out" : false,
  "_shards" : {
    "total" : 10,
    "successful" : 10,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "spx_id_administrative_division_detail_di",
        "_type" : "_doc",
        "_id" : "EXA3IXwBE40gA58bFtcI",
        "_score" : 1.0,
        "_source" : {
          "division_level" : "4",
          "country" : "Indonesia",
          "state" : "JAWA TIMUR",
          "city" : "KAB. SITUBONDO",
          "district" : "PANJI",
          "sub_district" : "MIMBAAN",
          "division_id" : "1855197536",
          "ds_version" : "202109131",
          "insert_time" : "2021-09-24 07:09:18",
          "grass_date" : "2021-09-23",
          "detail" : """{"type":"Polygon","coordinates":[[[114.0394515991212,-7.696899890899658],[114.0395889282229,-7.697700023651123],[114.0395736694336,-7.698019981384277],[114.0395736694336,-7.698130130767765],[114.0395202636719,-7.698740005493164],[114.0395126342773,-7.698910236358643],[114.0394973754883,-7.699692249298039],[114.0394897460938,-7.699940204620361],[114.0395431518555,-7.700890064239502],[114.0395965576174,-7.702030181884766],[114.0395965576174,-7.702529907226562],[114.0396270751954,-7.702743053436222],[114.039649963379,-7.702899932861328],[114.0396728515625,-7.703030109405461],[114.0397033691406,-7.704529762268066],[114.0397109985352,-7.705230236053353],[114.0397262573242,-7.705785751342773],[114.0397644042969,-7.70681715011591],[114.0397720336914,-7.707009792327881],[114.0397720336914,-7.707290172576904],[114.0397796630862,-7.707749843597355],[114.0397796630862,-7.708049774169922],[114.0397109985352,-7.7082200050354],[114.0396423339845,-7.708310127258244],[114.0396041870118,-7.708504199981689],[114.0395965576174,-7.7086501121521],[114.0394973754883,-7.708869934082031],[114.0394515991212,-7.708990097045842],[114.0393371582031,-7.709310054779053],[114.0391311645508,-7.709859848022404],[114.0390930175782,-7.709969043731689],[114.0389709472656,-7.710259914398193],[114.0388488769532,-7.710609912872314],[114.0387115478517,-7.710929870605412],[114.0385360717773,-7.711252689361572],[114.0383453369141,-7.711600780487004],[114.0382385253906,-7.711800098419133],[114.0380020141602,-7.711959838867188],[114.0379180908204,-7.712009906768742],[114.0376815795901,-7.712170124053955],[114.0375366210939,-7.71222019195551],[114.037239074707,-7.712339878082219],[114.0370025634766,-7.712510108947697],[114.0365219116211,-7.712969779968262],[114.036247253418,-7.713224411010742],[114.0358428955079,-7.713600158691349],[114.0356292724611,-7.713970184326172],[114.0351409912109,-7.715159893035889],[114.0348587036134,-7.715690135955754],[114.034507751465,-7.716119766235352],[114.0342178344727,-7.716509819030762],[114.0340194702148,-7.716899871826172],[114.0337829589844,-7.717339992523193],[114.0336532592773,-7.717589855194092],[114.0336303710938,-7.717959880828857],[114.0336914062501,-7.718739986419678],[114.0334930419924,-7.719759941101074],[114.0332107543945,-7.720729827880859],[114.032913208008,-7.721640110015869],[114.0326080322267,-7.722879886627197],[114.032470703125,-7.723259925842228],[114.0323104858398,-7.72370004653925],[114.0318603515625,-7.724170207977295],[114.0313720703125,-7.72464990615839],[114.0308532714845,-7.724929809570312],[114.0303421020508,-7.725190162658635],[114.029510498047,-7.725369930267277],[114.0285720825196,-7.725500106811467],[114.0282211303712,-7.725150108337289],[114.0280303955079,-7.724969863891602],[114.0275726318362,-7.724569797515869],[114.0270767211915,-7.724020004272461],[114.0265197753906,-7.723440170288086],[114.0261230468751,-7.722819805145264],[114.0260009765625,-7.722549915313721],[114.0258712768556,-7.722350120544377],[114.0256576538086,-7.721620082855225],[114.0254974365236,-7.720600128173828],[114.0254592895508,-7.720369815826302],[114.0254287719727,-7.720240116119328],[114.0252380371094,-7.720139980316105],[114.0251083374025,-7.72005987167347],[114.0250015258789,-7.719900131225586],[114.0249023437502,-7.719719886779785],[114.0247726440431,-7.719600200653019],[114.024383544922,-7.719550132751465],[114.0242767333984,-7.71953010559082],[114.0241699218751,-7.719419956207275],[114.0240783691406,-7.718850135803223],[114.0240173339845,-7.71868991851801],[114.023551940918,-7.718599796295166],[114.0233993530275,-7.718570232391301],[114.023323059082,-7.718500137329102],[114.0230636596681,-7.718259811401254],[114.0228424072267,-7.718080043792725],[114.0227737426758,-7.71798038482666],[114.0226669311525,-7.717810153961182],[114.0225677490234,-7.717629909515381],[114.0223922729492,-7.717450141906681],[114.0218963623048,-7.717219829559269],[114.0217285156251,-7.717150211334229],[114.0215072631837,-7.716670036315918],[114.0214309692383,-7.71658992767334],[114.0211715698242,-7.71658992767334],[114.0210037231445,-7.716599941253605],[114.0208511352539,-7.716519832611027],[114.0207290649415,-7.716370105743408],[114.0206680297852,-7.716259956359863],[114.0201721191407,-7.716149806976205],[114.0200424194336,-7.716092586517334],[114.019989013672,-7.71602010726923],[114.0199203491211,-7.715929985046387],[114.0198822021484,-7.715805530548039],[114.0198135375978,-7.715590000152531],[114.0197219848633,-7.715079784393311],[114.0196228027345,-7.714789867401123],[114.0195770263673,-7.714700222015324],[114.019256591797,-7.71461009979248],[114.0187988281252,-7.714439868927002],[114.0183181762695,-7.714290142059212],[114.0181732177734,-7.714237689971924],[114.0178298950195,-7.714119911193791],[114.017448425293,-7.71396017074585],[114.0170288085938,-7.713830947875977],[114.0166320800781,-7.713749885559025],[114.0164031982422,-7.713749885559025],[114.0163192749026,-7.713749885559025],[114.0157928466798,-7.713809967040959],[114.0153808593751,-7.713915824890137],[114.0152435302736,-7.713864803314209],[114.0148696899415,-7.713580131530705],[114.0138092041016,-7.712810039520264],[114.0132293701172,-7.712379932403508],[114.0128479003906,-7.71206617355341],[114.0127410888673,-7.711969852447453],[114.0121002197266,-7.711450576782227],[114.0119323730471,-7.711308479309082],[114.0114288330079,-7.710889816284123],[114.011199951172,-7.710714340209904],[114.0108566284181,-7.710460186004582],[114.0107803344727,-7.710393428802433],[114.0101165771484,-7.709859848022404],[114.0096435546877,-7.709479808807373],[114.0093231201173,-7.709249973297119],[114.0093688964844,-7.708909988403263],[114.0094299316406,-7.708549976348877],[114.0094985961915,-7.707858085632267],[114.0095520019531,-7.707382202148438],[114.0096282958984,-7.706769943237305],[114.0096740722657,-7.706521034240723],[114.0097579956055,-7.70608997344965],[114.0100555419923,-7.704571723937931],[114.010093688965,-7.704470634460449],[114.0102081298828,-7.704299926757812],[114.0103759765625,-7.703694343566895],[114.0104293823243,-7.703499794006348],[114.0105361938477,-7.703035831451416],[114.0106124877931,-7.702740192413273],[114.0106353759767,-7.702630519866943],[114.0106582641603,-7.702505588531437],[114.0107192993164,-7.702173709869385],[114.0107498168945,-7.702020168304387],[114.0109329223633,-7.701424598693791],[114.0110015869142,-7.701220035552979],[114.0110702514648,-7.701040744781494],[114.0111083984375,-7.700930118560791],[114.0111618041992,-7.700769901275635],[114.0111618041992,-7.70061016082758],[114.0113906860353,-7.700469970703068],[114.0115432739258,-7.700369834899902],[114.0117111206055,-7.700270175933838],[114.0119018554688,-7.700176239013615],[114.0127182006837,-7.699779987335205],[114.0130310058594,-7.699610233306885],[114.0132064819336,-7.699505805969238],[114.0134429931641,-7.699389934539738],[114.0140838623048,-7.699051856994572],[114.0144729614259,-7.698846817016602],[114.0150833129883,-7.698530197143555],[114.0151977539064,-7.698460102081242],[114.0152816772461,-7.698420047759953],[114.0154800415039,-7.698299884796143],[114.015739440918,-7.698160171508789],[114.0159301757812,-7.698052883148193],[114.0163574218751,-7.697810173034668],[114.0165405273439,-7.697720050811654],[114.0166244506836,-7.697648048400879],[114.0166931152344,-7.697612285614014],[114.0168914794924,-7.697529792785588],[114.0171203613282,-7.697509765624943],[114.0183563232422,-7.697340011596623],[114.0189590454102,-7.69720983505249],[114.0199813842775,-7.697110176086426],[114.0209121704103,-7.696899890899658],[114.0213623046875,-7.696800231933594],[114.0216827392579,-7.696750164031982],[114.0217971801758,-7.696730136871338],[114.022041320801,-7.696770191192627],[114.0221405029297,-7.696778297424203],[114.0224456787111,-7.696803569793644],[114.0225219726562,-7.696809768676758],[114.0229721069336,-7.696839809417725],[114.0236206054689,-7.696869850158635],[114.0237731933595,-7.696886539459172],[114.0250701904297,-7.697028636932259],[114.0251693725587,-7.69704008102417],[114.0254745483401,-7.697053432464543],[114.0256195068359,-7.697060108184814],[114.0255432128907,-7.697546482086125],[114.0251846313477,-7.699484825134221],[114.0280838012695,-7.698546886444035],[114.0287322998048,-7.698329448699894],[114.0290145874023,-7.698234081268254],[114.0293121337892,-7.698136329650879],[114.0295333862306,-7.698060989379883],[114.0297927856445,-7.697975635528508],[114.0301055908204,-7.69787073135376],[114.0309524536133,-7.697590351104679],[114.0314254760743,-7.697422504424992],[114.0317764282229,-7.697313785552979],[114.032470703125,-7.697113990783691],[114.0325469970704,-7.697083950042725],[114.0326843261719,-7.697017192840519],[114.0328140258789,-7.696988582611084],[114.0330047607422,-7.696918487548771],[114.0331268310548,-7.696875095367375],[114.0345916748047,-7.696402549743652],[114.0354995727539,-7.69608020782465],[114.0366287231445,-7.69569015502924],[114.0367736816407,-7.69564580917347],[114.0373916625977,-7.695459842681828],[114.0376892089846,-7.695380210876408],[114.0378875732423,-7.695330142974854],[114.0380706787112,-7.695276260375863],[114.0387268066406,-7.695079803466797],[114.0391387939453,-7.69497013092041],[114.0391616821289,-7.69516992568964],[114.039192199707,-7.695469856262207],[114.0392150878909,-7.695605278015137],[114.0392379760743,-7.695740222930908],[114.0392608642579,-7.696060180664006],[114.0393218994141,-7.696340084075928],[114.0392837524415,-7.696660041809025],[114.0394821166992,-7.696774482726994],[114.0394515991212,-7.696899890899658]]]}"""
        }
      }
    ]
  }
}
2

Hi @Liangliang_Qiu You are almost there, Try This...

Create a mapping with the geo_shape data type

DELETE discuss

PUT /discuss
{
  "mappings": {
    "properties": {
      "detail": {
        "type": "geo_shape"
      }
    }
  }
}

Then create an ingest pipeline see here that will parse the json field it is already in the proper format for the geo_shape data type.

DELETE _ingest/pipeline/discuss

PUT _ingest/pipeline/discuss
{
  "processors": [
    {
      "json": {
        "field": "detail"
      }
    }
  ]
}

Insert the document with the ingest pipeline you specific the pipeline on ingest or you can even just set it as the default pipeline for this index but here is a test.

I had to shorten the polygon because the line was to long for the editor.

POST discuss/_doc?pipeline=discuss
{
  "division_level": "4",
  "country": "Indonesia",
  "state": "JAWA TIMUR",
  "city": "KAB. SITUBONDO",
  "district": "PANJI",
  "sub_district": "MIMBAAN",
  "division_id": "1855197536",
  "ds_version": "202109131",
  "insert_time": "2021-09-24 07:09:18",
  "grass_date": "2021-09-23",
  "detail" : """{"type":"Polygon","coordinates":[[[114.0394515991212,-7.696899890899658],[114.0395889282229,-7.697700023651123],[114.0395736694336,-7.698019981384277],[114.0395736694336,-7.698130130767765],[114.0395202636719,-7.698740005493164],[114.0395126342773,-7.698910236358643],[114.0394973754883,-7.699692249298039],[114.0394897460938,-7.699940204620361],[114.0395431518555,-7.700890064239502],[114.0395965576174,-7.702030181884766],[114.0395965576174,-7.702529907226562],[114.0396270751954,-7.702743053436222],[114.039649963379,-7.702899932861328],[114.0396728515625,-7.703030109405461],[114.0397033691406,-7.704529762268066],[114.0397109985352,-7.705230236053353],[114.0397262573242,-7.705785751342773],[114.0397644042969,-7.70681715011591],[114.0397720336914,-7.707009792327881],[114.0397720336914,-7.707290172576904],[114.0397796630862,-7.707749843597355],[114.0397796630862,-7.708049774169922],[114.0397109985352,-7.7082200050354],[114.0396423339845,-7.708310127258244],[114.0396041870118,-7.708504199981689],[114.0395965576174,-7.7086501121521],[114.0394973754883,-7.708869934082031],[114.0394515991212,-7.708990097045842],[114.0393371582031,-7.709310054779053],[114.0391311645508,-7.709859848022404],[114.0390930175782,-7.709969043731689],[114.0389709472656,-7.710259914398193],[114.0388488769532,-7.710609912872314],[114.0387115478517,-7.710929870605412],[114.0385360717773,-7.711252689361572],[114.0383453369141,-7.711600780487004],[114.0382385253906,-7.711800098419133],[114.0380020141602,-7.711959838867188],[114.0379180908204,-7.712009906768742],[114.0376815795901,-7.712170124053955],[114.0375366210939,-7.71222019195551],[114.037239074707,-7.712339878082219],[114.0370025634766,-7.712510108947697],[114.0365219116211,-7.712969779968262],[114.036247253418,-7.713224411010742],[114.0358428955079,-7.713600158691349],[114.0356292724611,-7.713970184326172],[114.0351409912109,-7.715159893035889],[114.0394515991212,-7.696899890899658]]]}"""
}

Then I created an index pattern... added that layer, and the polygon shows up on the map.
Noted this was a shortened polygon for a test.

Screen Shot 2021-09-26 at 9.20.06 AM
Screen Shot 2021-09-26 at 9.20.06 AM1920×1331 132 KB

1 Like
3

thanks!!

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.