postgresql - How do I filter jsonb with multiple criteria? -

-

i have following table structure:

create table mytable (   id   serial primary key,   data jsonb );

and following data (partial brevity...notice randomness of years , sales/expense yrs don't align each other):

insert mytable (data) values ('{"employee": "jim romo",   "sales": [{"value": 10, "yr": "2012"}, {"value": 5, "yr": "2013"}, {"value": 40, "yr": "2014"}],  "expenses": [{"value": 2, "yr": "2007"}, {"value": 1, "yr": "2013"}, {"value": 3, "yr": "2014"}],   "product": "tv", "customer": "1", "updated": "20150501" }'), ('{"employee": "jim romo",   "sales": [{"value": 10, "yr": "2012"}, {"value": 5, "yr": "2013"}, {"value": 41, "yr": "2014"}],  "expenses": [{"value": 2, "yr": "2009"}, {"value": 3, "yr": "2013"}, {"value": 3, "yr": "2014"}],   "product": "tv", "customer": "2", "updated": "20150312" }'), ('{"employee": "jim romo",   "sales": [{"value": 20, "yr": "2012"}, {"value": 25, "yr": "2013"}, {"value": 33, "yr": "2014"}],  "expenses": [{"value": 8, "yr": "2012"}, {"value": 12, "yr": "2014"}, {"value": 5, "yr": "2009"}],   "product": "radio", "customer": "2", "updated": "20150311" }'), ('{"employee": "bill baker",   "sales": [{"value": 1, "yr": "2010"}, {"value": 2, "yr": "2009"}, {"value": 3, "yr": "2014"}],  "expenses": [{"value": 3, "yr": "2011"}, {"value": 1, "yr": "2012"}, {"value": 7, "yr": "2013"}],   "product": "tv", "customer": "1", "updated": "20150205" }'), ('{"employee": "bill baker",   "sales": [{"value": 10, "yr": "2010"}, {"value": 12, "yr": "2011"}, {"value": 3, "yr": "2014"}],  "expenses": [{"value": 4, "yr": "2011"}, {"value": 7, "yr": "2009"}, {"value": 4, "yr": "2013"}],   "product": "radio", "customer": "1", "updated": "20150204" }'), ('{"employee": "jim romo",  "sales": [{"value": 22, "yr": "2009"}, {"value": 17, "yr": "2013"}, {"value": 35, "yr": "2014"}],  "expenses": [{"value": 14, "yr": "2011"}, {"value": 13, "yr": "2014"}, {"value": 8, "yr": "2013"}],   "product": "tv", "customer": "3", "updated": "20150118" }')

for each employee need evaluate most-recently updated row , find employees 2014 tv sales greater 30. there need further filter employees average tv expenses of less 5. average need take of tv expenses , not latest row.

my expected output 1 row:

employee    | customer | 2014 tv sales   |  2013 avg tv expenses ------------+----------+-----------------+---------------------- jim romo    |    1     |   40            |  4

i can (kindof) 1 or other not both:

a. 2014 sales > 30 (but unable recent "tv" sales ;(

select * mytable (select (a->>'value')::float     (select jsonb_array_elements(data->'sales') a) b      @> json_object(array['yr', '2014'])::jsonb) > 30

b. avg 2013 expense (this needs avg tv expenses)

select avg((a->>'value')::numeric)     (select jsonb_array_elements(data->'expenses') mytable) b   @> json_object(array['yr', '2013'])::jsonb

edit: potentially large table commentary on performance , indexing needs appreciated new both postgresql , jsonb.

edit #2: i've tried both answers , neither seem efficient large table ;(

this (rather lengthy) answer problem. comments inside query should explain different pieces. basic ideas followed are: 1) keep simple each operation, try first produce correct result, optimize; 2) trasform as possible (but not much) json structure in more "relational like" structure, since relations have more powerful operators json data in postgres. of corse there space simplify query , produce more efficient version, @ least starting point.

with mytable1   -- transform table in more "relational-like" structure (just clarity)   (select id, data->>'employee' employee, data->>'product' product,        (data->>'updated')::integer updated, (data->>'customer')::integer customer,           data->'sales' sales, data->'expenses' expenses     mytable), avg_exp_for_2013_tv -- find average expenses tv in 2013 each employee    (select employee, avg(expenses.value) avg2013_expenses     mytable1 , jsonb_to_recordset(expenses) expenses(yr text, value float)     product = 'tv' , expenses.yr = '2013'     group employee), most_recent_updates_employees  -- find recent updates each employee     (select employee, max(updated) updated     mytable1 t1     group employee), most_recent_updated_rows   -- find rows recent updates    (select t1.*     mytable1 t1, most_recent_updates_employees m     t1.employee = m.employee , t1.updated = m.updated), employees_with_2014_tv_sales_gt_30    (select employee, customer, sales.value sales_value     most_recent_updated_rows m, jsonb_to_recordset(m.sales) sales(yr text, value float)     yr = '2014' , value > 30) select e1.employee, e1.customer, e1.sales_value "2014 tv sales", e2.avg2013_expenses "2013 avg tv expenses" employees_with_2014_tv_sales_gt_30 e1, avg_exp_for_2013_tv e2 e1.employee = e2.employee , avg2013_expenses < 5

Comments

Post a Comment

Popular posts from this blog

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null
Read more

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops',
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens
Read more