I have the following record as a jsonb field in a PG13 table

meta 
{"subscriptions": [{"location_id": 14555, "practice_area_id": 39, "monthly_quota_views": 300, "remaining_views": 300}, 
                   {"location_id": 14832, "practice_area_id": 39, "monthly_quota_views": 250, "remaining_views": 250}, 
                   {"location_id": 17793, "practice_area_id": 39, "monthly_quota_views": 700, "remaining_views": 700}]}

I'd like to query for location_id: 14555, practice_area_id: 39 and having remaining_views > 0. Also, I'd like to return the selected hash from the array as part of the result (if possible).

I have

SELECT * FROM mytable WHERE 
meta->'subscriptions' @> '[{"location_id":14555}]' and 
meta->'subscriptions' @> '[{"practice_area_id":39}]';

but how would I check for remaining_views > 0 and return that selected hash as part of the result? Is there a less bulky way of writing this query? Thanks

CodePudding user response：

You can use a JSON path expression to filter the desired elements and also return only those:

select jsonb_path_query_array(meta, '$.subscriptions[*] ? (@.location_id == 14555 && @.practice_area_id == 39 && @.remaining_views > 0)')
from the_table
where meta @? '$.subscriptions[*] ? (@.location_id == 14555 && @.practice_area_id == 39 && @.remaining_views > 0)';

Based on your sample data, the above returns:

jsonb_path_query_array                                                                              
----------------------------------------------------------------------------------------------------
[{"location_id": 14555, "remaining_views": 300, "practice_area_id": 39, "monthly_quota_views": 300}]

If you are certain there will be only one element matching your condition, you can use jsonb_path_query_first() instead

CodePudding user response：

I suspect by "return the selected hash from the array" you refer to the object that is contained in the subscriptions array.

There are two ways to do this:

• use jsonb_array_elements to get the individual objects:

  SELECT *
  FROM pg13,
  LATERAL jsonb_array_elements(pg13.meta -> 'subscriptions') el
  WHERE el->>'location_id' = '14555'
    AND el->>'practice_area_id' = '39'
    AND (el->>'remaining_views')::int > 0
  

• use a json path query:

  SELECT *, jsonb_path_query(meta, '$.subscriptions[*] ? (@.location_id == 14555 && @.practice_area_id == 39 && @.remaining_views > 0)')
  FROM pg13