0

Lets say we have several primary tables in our database (a, b and c), and then another (x) that stores a complex, only semi-predictable JSONb object that has references to all the primary tables. In my case the JSONb looks something like this:

{ entries: [ [ [ {table: 'a', id: '1'}, {table: 'b', id: '4', entries: [ {table: 'a', id: '3'}, {table: 'a', id: '1'} ... ]}, {table: 'c', id: '5', entries: [ {table: 'a', id: '2'}, {table: 'b', id: '4', entries: [ {table: 'a', id: '1'}, {table: 'a', id: '6'}, ... ]}, ... ]}, ... ], ... ], ... ] }

When selecting records from table x we want to filter the results by properties of the other tables - eg. only x records that contain an a record that has a field containing a particular enum value.

Is it even possible or at all performant to do this in a single query using this JSONb data structure? It seems it would require some serious aggregating of IDs, and doing that for every query seems like a lot of work.

The alternative I was considering is to keep the JSONb field as it is, but also make join tables (I may have the name wrong there) to track all of the table x records dependencies. So you'd have tables x_a, x_b, x_c, etc. and only store a single unique join record for each a, b, or c, ID that pops up in the x record. This way when writing the queries, a simple and non-JSON approach could be used to do a regular join filter.

As a beginner to intermediate SQL programmer this seems like it would at least lead to more readable code, however I'm not sure if this counts as violating the "only enter the data once" rule.

Any and all input is welcome, including reading material on how to make these decisions.

Language is PostgresQl

Improve this question
1
  • Could use a JSONB path query with a descendant (recursive) wildcard dbfiddle.uk/P0ihNfmu but it's going to be very inefficient. Commented Jan 7, 2024 at 22:34

1 Answer 1

Reset to default
1

The alternative I was considering is to keep the JSONb field as it is, but also make join tables (I may have the name wrong there) to track all of the table x records dependencies. So you'd have tables x_a, x_b, x_c, etc. and only store a single unique join record for each a, b, or c, ID that pops up in the x record.

Yes, this is the way. Your JSONb field is denormalized and will perform poorly if you try to join tables off of it. It is likely not possible to do so in a sargable way and will need to unbox the JSON and scan the entire table each time you join to it.

The "join" tables you referred to are called bridge tables or linking tables. They will likely help de-duplicate your data of the JSON field too. They will be indexable on their key fields, and should be performant when joined to. JSON is actually a pretty poor way to store any data that needs to be operated on, in general. It's really best practice to only use it for data that is only written and read, not operated on, when that data can't be easily normalized otherwise.

however I'm not sure if this counts as violating the "only enter the data once" rule.

You don't want to maintain the same data in two places, when possible, correct. But why continue to even use the JSON column though? The consumer shouldn't care about the object that holds the data. And if it does, you can just write a query that creates a JSON structured result set off of the tables. You can even save this query into a view object so you can easily reference it as needed. Doing so allows you to not have to maintain the data itself in two places then, which could otherwise lead to data integrity and management issues.

Improve this answer
2
  • Thank you, great answer. I read up on sargable. Mostly now you have me considering if I need the JSON. It would be quite difficult to describe all the arrays and objects via bridge tables, and I'd need to create a few more primary tables to represent some of the intermediate hierarchy. Those intermediate peices of hierarchy don't need to be operated on, they are truely read/write. Is this a good place to use the JSON since it will save me making more tables and writing more complicated multi-level joins later? Commented Jan 6, 2024 at 20:32
  • 1
    @Henry np! If you have some dynamic or hard to structure types of data that you're only using the database to store and later on recall from, to display in the app, and truly don't need to do any kind of operations against that data and don't care about data integrity with that data, then yes JSON is a fair enough choice. Commented Jan 6, 2024 at 21:46

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.