select values in json into other json (Postgres)

Question

I have the following JSON

{
  "eventSummaryList": [
    {
      "customer": "189256",
      "data": "{\"cliente\":\"189256\",\"data_posicao\":\"1491426372\",\"gps_valido\":\"1\",\"horimetro\":\"120561\",\"ibuttonHex\":\"0\",\"ibuttonPart1\":\"0\",\"ibuttonPart2\":\"0\",\"id_evento\":\"null\",\"id_motorista\":\"0\",\"ignicao\":\"1\",\"latitude\":\"-2222222\",\"longitude\":\"-2222222\",\"odometro\":\"253692\",\"pos_memoria\":\"0\",\"veiculo\":\"44444\",\"velocidade\":\"50\"}",
      "identifierEventRule": "77404",
      "identifierRule": "6",
      "identifierSummary": "28901976",
      "rule": "velocidade_maior_que",
      "status": 1,
      "vehicle": "44444"
    }
  ],
  "header": {
    "mensagem": {
      "estilo": "SUCCESS",
      "mensagem": "Successfully executed service",
      "plataforma": "EVENT_POINT",
      "status": "SUCESSO"
    }
  }
}

And I need to extract the value "velocidade" what's inside "data" that contains another json.

I'm using the following syntax but it returns null.

select  cast((value::json ->'data')::json->> 'velocidade' AS   int)    AS velocidade,

May have better luck with: select (select value::json ->'data'::json)->>velocidade as velocidade from ... — Joe Love, Apr 10 '17 at 15:54
The content in `data` is invalid json so you need to first cleanup that before you can extract any data from it using json operators. e.g. replacing the `\"` with a simple `"` and replacing `"{` with just `{` — , Apr 10 '17 at 16:05
I did the following, but I think there should be something simpler... `regexp_replace(regexp_replace(regexp_replace('data'::text ,'\\', '', 'g'), '"{', '{' ), '}"' , '}')::json ->>'velocidade'` — luiz kava, Apr 10 '17 at 16:31

score 1 · Accepted Answer · answered Apr 11 '17 at 11:16

Try:

SELECT ((value::json #>> '{eventSummaryList,0,data}')::json ->> 'velocidade')::int

(The #>> or ->> operators are the key. If you use #> or ->, you'll end up with a json-encoded json string. BTW this is really a messed up model, I would look into fixing its input first/instead.)

http://rextester.com/THVYFK9026

select values in json into other json (Postgres)

1 Answers1