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

select data from internal json in jsonb postgresql

I have the following table structure:

create table system_log
(
    id bigserial primary key,
    details jsonb
);

Data in ‘details’ column is json with inner json:

{
  "prop1": "value1",
  "prop2": "value2",
  "body": "{"prop3": "value3"}"
}

How to select data from inner json like ‘select details.body.prop3 …’ ?

Tags:

3

Answers


  1. 0

    You can use the arrow operators:

    SELECT details -> 'body' ->> 'prop3'
FROM ... WHERE ...

    -> retrieves the result as jsonb, ->> retrieves the result as text.

    Login or Signup to reply.
  2. 0 
    SELECT (details->'body'::text)::jsonb->>'prop3' 
FROM system_log;
    Login or Signup to reply.
  3. 0

    Having

    insert into system_log values (1, 
'{
"prop1":"value1",
"prop2":"value2",
"body":{"prop3":"value3"}
}');

    You could query like this

    select jsonb_path_query(details ,'$.body.prop3 ') 
from system_log sl;

    Or also use a condition

    select jsonb_path_query(details ,'$.body.prop3 ? (@ =="value3")') 
from system_log sl;

    Working sample here
    and the docs

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

Back To Top
Search