Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2015
    Posts
    1

    Unanswered: how to query nested levels in jsonb datatype column

    For the following schema help me query the following

    i couldn't figure this out, select all from orders table where details->quantity is greater than 2?

    i tried select * from orders Where document->details->'quantity'>'2'--- didn't work

    the following worked
    select * from orders Where document->'orderid'='3'
    this gets me the row

    select * from orders Where document->'key'<'90'
    this gets me the row


    create table orders (document jsonb)

    insert into orders values ('{"orderid":3,"key":100,"total":3510.20,"ref_id": "AFV",
    "details":
    [
    {"product":3,"quantity":20,"price":2.1,"c":"someth ing"},
    {"product":13,"quantity":2,"price":1.1},
    {"product":18,"quantity":4,"price":0.3}
    ]
    }')
    Insert Into orders values ('{"orderid":2, "key":20, "total":510.20, "ref_id":"zzz"}')
    insert into orders values ('{"orderid":5, "key":100, "total":3510.20, "ref_id":"AFV",
    "details":
    [
    {"product":3,"quantity":20,"price":2.1},
    {"product":13,"quantity":2,"price":1.1},
    {"product":18,"quantity":4,"price":0.3}
    ]
    }')
    insert into orders values ('{"ref_id": "AFV", "total": 3510.20,
    "details":
    [
    {"c": "something", "price": 2.1, "product": 3, "quantity": 20},
    {"price": 1.1, "product": 13, "quantity": 2},
    {"price": 0.3, "product": 18, "quantity": 4}
    ], "orderid": 3, "key": 100}')

  2. #2
    Join Date
    Jul 2015
    Posts
    7
    You can try with this to get "quantity is greater than 2":

    Code:
    SELECT t.document,elem
    FROM orders t, jsonb_array_elements(t.document->'details') elem
    where elem ->> 'quantity' > '2';

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •