Json extraction using postgres

Question

I have a json

{
  "timer": {
    "19272": {
      "asset_id": 9354,
      "original_total_time": 5,
      "original_warning_time": null
    }
  },
  "SEBKeys": [],
  "scoreMappings": []
}

I need to extract 'original_total_time' value from this json using Postgres.

Can anyone help?

If you're using PHP, you can use [`json_decode()`](https://www.php.net/manual/en/function.json-decode.php). — yaakov, May 12 '21 at 03:41
I got the solution (SELECT * , requests.request -> 'notes' -> json_object_keys((requests.request ->> 'notes')::json) ->> 'message' AS message FROM requests) — Rakhi SI, May 12 '21 at 04:10

score 0 · Answer 1 · answered May 12 '21 at 04:06

0

If your JSON structure is fixed then you can use following query

select json_->'timer'->'19272'->>'original_total_time' from test

DEMO

answered May 12 '21 at 04:06

Akhilesh Mishra

5,876
3
16
32

Json extraction using postgres

1 Answers1