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

How use a where function with just a part of json value? – Postgresql

How are you doing?

I’m currently using PostgreSQL and want to create a 'where' with just a specif date but the value returns me a kind of timestamp.
Example of the json:

What the json looks like:

{"key1": {"key2": "2023-02-13T13:28:49.087000Z"}}

What I’m doing:

select *
from table
where json(column) -> 'key1' ->> 'key2' = '2023-02-14'

Ps: the json column ‘details’ is in text format so I converted i into json

I want to get the results where the key2 value is ‘2023-02-14’.

Tags:

2

Answers


  1. 0

    You can use the LIKE operator:

    select *
from the_table
where details::json -> 'key1' ->> 'key2' like '2023-02-14%'

    Or a bit shorter:

    select *
from the_table
where details::json #>> '{key1,key2}' like '2023-02-14%'
    Login or Signup to reply.
  2. 0

    There are 2 steps required to what you you want.

    Step 1. Cast string column to json using PostgreSQL json operator and fetch the needed column.

    col_a::json->'key1'->>'key2'

    Step 2: use PostgreSQL substring() function to fetch the date part of the string, full query example as below:

    select *
from table
where substring(col_a::json->'key1'->>'key2',1,10) = '2023-02-14'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search