2
\$\begingroup\$

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?

\$\endgroup\$
5
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$ Feb 11 '20 at 22:12
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.