Use JSON file in postgresql database

Question

I have a table in a postgresql database with one field with json format. It's look like this :

[
  {
    "transition": "transition1",
    "from": "step1",
    "to": "step2",
    "date": {
      "date": "2021-01-30 15:34:06.840859"
    }
  },
  {
    "transition": "transition2",
    "from": "step2",
    "to": "step3",
    "date": {
      "date": "2021-01-30 16:52:00.412208"
    }
  }
]

I want to have a new column with the date of transition1. I tried a lot of things but I can't figure out how to extract this date, I can't use the index because the number of transition is not fixed, some user could have 3 and other more than 10.

What is your Postgres version?

user330315
– user330315

2021-02-18 15:16:04 +00:00
Commented Feb 18, 2021 at 15:16 — user330315
– user330315, Commented Feb 18, 2021 at 15:16

score 2 · Accepted Answer · 2021-02-18 18:44:25Z

2

You need to iterate over all array elements to find the correct one:

select (select (item -> 'date' ->> 'date')::timestamp
        from jsonb_array_elements(jsonb_column) as x(item)
        where item ->> 'transition' = 'transition1') as transition_date
from the_table
;

If you column is defined as json rather than jsonb (which it should be) you need to use json_array_elements() instead.

edited Feb 18, 2021 at 18:44

answered Feb 18, 2021 at 15:19

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

J.doe Over a year ago

My version is : 11.6 Can I use the same thing not to update the table but select this value ?

user330315 Over a year ago

@J.doe: yes you can put that expression into a SELECT

Collectives™ on Stack Overflow

Use JSON file in postgresql database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related