https://www.percona.com/blog/storing-and-using-json-within-postgresql-part-one/
https://www.percona.com/blog/storing-and-using-json-within-postgresql-part-two
Recap & What’s Next
A few quick takeaways:
Recapping part 1:
- Using JSONB is probably going to be your best option in most use cases.
- Be very careful of type conversions and making assumptions on the data within your JSON/JSONB columns. You may get errors or odd results.
- Use the available indexes, generated columns, and expression indexes to gain substantial performance benefits.
What we learned in part 2:
- The more straightforward the JSON, the easier it will be to pull out and interact with the data you need.
- Nested JSON data can be pulled out in a few different ways. jsonb_to_rescordset is the easiest way I found to pull out the data I need. However, using this function or others is very susceptible to the data structure in your document (data types matter!).
- JSON data whose format changes ( elements added or removed ) may make using some functions difficult, if not impossible, to use.
- JSON within a well-built, designed application and database can be wonderful and offer many great benefits. JSON just dumped into databases won’t scale. Database design still matters.
Now that we have covered the basics and a few nuances of JSON with PostgreSQL, next up, it is time to look at the same functionality and data within MongoDB before finally comparing the three databases. Stay tuned!
weicode 