Filter a top level JSONB array document in Postgres?

Question

I have a Postgres table named 'events' with a column of 'id', 'name', 'invoices':

                                           Table "public.events"
        Column         |            Type             |                           Modifiers
-----------------------+-----------------------------+---------------------------------------------------------------
 id                    | integer                     | not null default nextval('events_id_seq'::regclass)
 name                  | character varying(255)      | not null
 invoices              | jsonb                       | not null

Each row has a invoices column of type jsonb with an array as a top level element:

Example Content:

[{"InvoiceId":4,"CustomerId":14,"InvoiceDate":"2009-01-06T00:00:00","BillingAddress":"8210 111 ST NW","BillingCity":"Edmonton","BillingState":"AB","BillingCountry":"Canada","BillingPostalCode":"T6G 2C7","Total":8.91}, 
 {"InvoiceId":5,"CustomerId":23,"InvoiceDate":"2009-01-11T00:00:00","BillingAddress":"69 Salem Street","BillingCity":"Boston","BillingState":"MA","BillingCountry":"USA","BillingPostalCode":"2113","Total":13.86}, 
 {"InvoiceId":8,"CustomerId":40,"InvoiceDate":"2009-02-01T00:00:00","BillingAddress":"8, Rue Hanovre","BillingCity":"Paris","BillingState":null,"BillingCountry":"France","BillingPostalCode":"75002","Total":1.98}, 
 {"InvoiceId":9,"CustomerId":42,"InvoiceDate":"2009-02-02T00:00:00","BillingAddress":"9, Place Louis Barthou","BillingCity":"Bordeaux","BillingState":null,"BillingCountry":"France","BillingPostalCode":"33000","Total":3.96}, 
 {"InvoiceId":10,"CustomerId":46,"InvoiceDate":"2009-02-03T00:00:00","BillingAddress":"3 Chatham Street","BillingCity":"Dublin","BillingState":"Dublin","BillingCountry":"Ireland","BillingPostalCode":null,"Total":5.94}, 
 {"InvoiceId":11,"CustomerId":52,"InvoiceDate":"2009-02-06T00:00:00","BillingAddress":"202 Hoxton Street","BillingCity":"London","BillingState":null,"BillingCountry":"United Kingdom","BillingPostalCode":"N1 5LH","Total":8.91}, 
 {"InvoiceId":13,"CustomerId":16,"InvoiceDate":"2009-02-19T00:00:00","BillingAddress":"1600 Amphitheatre Parkway","BillingCity":"Mountain View","BillingState":"CA","BillingCountry":"USA","BillingPostalCode":"94043-1351","Total":0.99}, 
 {"InvoiceId":14,"CustomerId":17,"InvoiceDate":"2009-03-04T00:00:00","BillingAddress":"1 Microsoft Way","BillingCity":"Redmond","BillingState":"WA","BillingCountry":"USA","BillingPostalCode":"98052-8300","Total":1.98}, 
 {"InvoiceId":15,"CustomerId":19,"InvoiceDate":"2009-03-04T00:00:00","BillingAddress":"1 Infinite Loop","BillingCity":"Cupertino","BillingState":"CA","BillingCountry":"USA","BillingPostalCode":"95014","Total":1.98}, 
 {"InvoiceId":16,"CustomerId":21,"InvoiceDate":"2009-03-05T00:00:00","BillingAddress":"801 W 4th Street","BillingCity":"Reno","BillingState":"NV","BillingCountry":"USA","BillingPostalCode":"89503","Total":3.96}]

How do i return a request set with a list of each rows id, name, and array of invoices that have a "Total" Greater than 5.00?

You mean all events with any invoice bigger than 5? Or all events which sum of invoices is bigger than 5? And filter only those invoices bigger than 5 or return all invoices regardless of the total? — Clodoaldo Neto
– Clodoaldo Neto, Commented Mar 6, 2017 at 19:47

Clodoaldo Neto · Accepted Answer · 2017-03-06 19:48:52Z

3

select id, name, invoices
from
    events
    cross join lateral
    jsonb_array_elements(invoices) o (o)
group by 1,2,3
having sum((o ->> 'Total')::numeric > 5)

answered Mar 6, 2017 at 19:48

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

Filter a top level JSONB array document in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related