How to extract a JSON object subset in Postgres (>12)

Question

I have the following structure and query:

SELECT jsonb_path_query_array(jsonb '{"subscores": {"score_a": 2, "score_b": 3, "score_c": 4}}','$.subscores.* ? (@>2)');

which returns: [3,4] or

SELECT jsonb_path_query(jsonb '{"subscores": {"score_a": 2, "score_b": 3, "score_c": 4}}','$.subscores.* ? (@>2)');

which returns (as rows):

3
4

Is there a way to structure my query to get the object subset as a single row, single field value? i.e.:

{"score_b": 3, "score_c": 4}

given a dynamic set of scores, ie. some rows will have score_d, ..., etc

Answer 1

I think you need to unnest all elements and the aggregate them back:

SELECT jsonb_object_agg(key, value) 
from (
 select *
 from jsonb_each(jsonb '{"subscores": {"score_a": 2, "score_b": 3, "score_c": 4}}' -> 'subscores')
) as p
where (p.value)::int > 2

• RuiDC: Thanks, that definitely works but will not perform well at scale, so will be curious to see if there's any better suggestion in the next few days otherwise will accept your answer. I'd move the WHERE clause to the subquery though to filter sooner.