Postgres query to extract a JSON sub-object from a JSON field

Question

I have a json field in a table that I want to select certain fields from into a new json field. So far I have this, which works, but I feel may not be optimal:

select to_json(new_obj) from (
 select big_json_field->'json_property_1' as json_property_1, 
 big_json_field->'json_property_2' as json_property_2 
 from table_with_big_json_field
) as new_obj

Is there a function or method I'm missing to optimize this?

Answer 1

I'd recommend (if it isn't already) converting to the type from json to jsonb and adding a GIN index. The documentation below lays it out pretty clear. Good luck! https://www.postgresql.org/docs/9.4/datatype-json.html

For my recommendation, reason for using jsonb as opposed to vanilla json is that regular json is stored as an exact copy in postgres and you cannot add an index to that column. Whereas jsonb has a smaller footprint and allows for indexing the specific column. I'd prefer to store it in a format that could be indexed to allow for queries to be more performant.

• Robert Smith: Yea sure thing. One caveat is looking at the poster's response below, I think he was looking more for advice on how to build a json object as opposed to looking for a performance gain. For my recommendation, reason for using jsonb as opposed to vanilla json is that regular json is stored as an exact copy in postgres and you cannot add an index to that column. Whereas jsonb has a smaller footprint and allows for indexing the specific column. I'd prefer to store it in a format that could be indexed to allow for queries to be more performant.

Answer 2

I ended up using jsonb_build_object (also available as json_build_object):

select jsonb_build_object (
 'json_property_1', big_json_field->'json_property_1', 
 'json_property_2', big_json_field->'json_property_2' 
) from table_with_big_json_field

This eliminated the sub-select, and felt a bit cleaner than as-ing every field.