4 ms·
> Are there any technical drawbacks if one should index JSON data in the following manner for storage in a SQL/NoSQL backend? It depends on whether the record
by lutusp 13y ago
> Are there any technical drawbacks if one should index JSON data in the following manner for storage in a SQL/NoSQL backend?
It depends on whether the record you chose is typical. It's a good idea to scan all the JSON records and detect any surprises before committing to a given table design.
And there's no reason to store the array's contents twice. A properly designed table should allow the fields to be accessible regardless of how they're stored. It's up to you whether to store them as separate (and separately named) fields or as an array. But some databases, MySQL included, don't have an array datatype, so if you decide to use MySQL you would want to break out each array member as a separate string field.
Finally, you need to add a primary key field to your design, for multiple reasons.
- wilsonfiifi 13y agoThe array is stored in full and it's contents are also parsed and stored separately to enable the following queries (pseudo code) to be performed: Get user's skills: Select row where path == 'skills' and 'objected' column == (uuid) Get user's skill at index 0: Select row where path = 'skills.[0]' and 'objected' column == (uuid) Get all users whose skills contain 'combat': Select rows where path starts with 'skills.[*]' and 'combat' in 'values' column
- lutusp 13y agoYes, that's fine, unless you plan to use a database engine like MySQL that doesn't support arrays. You have the option of using an SQL statement that doesn't require the fields to be in the same array. In other words, don't let your logic be defined by something that databases don't support.