Question 89189 in PostgreSQL Question posted in
The official documentation can be found here.

How to pull JSON values when there are multiple same values – Postgresql

I’m trying to pull out from this JSON column – "toTimestamp" & "fromTimestamp" so I can calculate instances when the video is above a certain number of seconds. (Postgresql)

*Metadata column

"videoMetadata": [
   {
      "cameraId":"74",
      "toTimestamp":"2023-02-15T13:06:43.050Z",
      "fromTimestamp":"2023-02-15T13:06:39.050Z"
   },
   {
      "cameraId":"236",
      "toTimestamp":"2023-02-15T13:06:43.050Z",
      "fromTimestamp":"2023-02-15T13:06:39.050Z"
   },
   {
      "cameraId":"12",
      "toTimestamp":"2023-02-15T13:06:43.050Z",
      "fromTimestamp":"2023-02-15T13:06:39.050Z"
   },
   {
      "cameraId":"8",
      "toTimestamp":"2023-02-15T13:06:43.050Z",
      "fromTimestamp":"2023-02-15T13:06:39.050Z"
   }
],

I tried to use metadata->’videoMetadata’->>’toTimestamp’ as XXX
But I get null values only and I suspect it is because the ‘toTimestamp’ is presented more than once.

Tags:

2

Answers


  1. 0

    videoMetadata is an array, so you need to specify from which array element you want to take the timestamps. If you don’t care which one you get, just take the first:

    SELECT metadata -> 'videoMetadata' -> 0 ->> 'toTimestamp' as start, 
       metadata -> 'videoMetadata' -> 0 ->> 'fromTimestamp' as end 
from the_table;
    Login or Signup to reply.
  2. 0

    try this:

    select (jsonb_array_elements(json_row->'videoMetadata'))->>'cameraId' as cameraId,
       (jsonb_array_elements(json_row->'videoMetadata'))->>'toTimestamp'  as toTimestamp,
       (jsonb_array_elements(json_row->'videoMetadata'))->>'fromTimestamp' as fromTimestamp
 from table_Name

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search