Storing Json in Database Vs. Having a New Column For Each Key

properties table pattern vs storing all properties in json column

As long as you don't try to use the properties for searching or sorting, there's not much difference.

As you said, putting a JSON column in your model table allows you to avoid a JOIN to the properties table.

I think your properties table actually needs to have one more column, to name the property. So it should be:

key (string), 
property (string),
value(string), 
type (string) - tells if the value is of string, integer, boolean, json type 

The downsides are pretty similar for both solutions.

• Queries will be more complex with either solution, compared to querying normal columns.

• Storing non-string values as strings is inefficient. It takes more space to store a numeric or datetime value as a string than as a native data type.

• You can't apply constraints to the properties. No way to make a property mandatory (you would use NOT NULL for a normal column). No way to enforce uniqueness or foreign key references.

There's one case I can think of that gives JSON an advantage. If one of your custom properties is itself multi-valued, there's a straightforward way to represent this in JSON: as an array within your JSON document. But if you try to use a property table, do you store multiple rows for the one property? Or serialize the set of values into an array on one row? Both solutions feel pretty janky.

Because the "schemaless properties" pattern breaks rules of relational database design anyway, there's not much you can do to "do it right." You're choosing the lesser of two evils, so you can feel free to use the solution that makes your code more convenient.

json field type vs. one field for each key

1. First of all having table with 100 columns may suggest you should rethink your architecture before proceeding. Otherwise it will only become more and more pain in later stages.

2. May be you are storing data as seperate columns which can be broken down to be stored as seperate rows.

3. I think the sql query you are writing is like (select * ... ) where you may be fetching extra columns than you may require. You may specify the columns you require. It will definitely speed up the api response.

4. In my personal view storing active data in json inside sql is not useful. Json should be used as last resort for the meta data which does not mutate or needs not to be searched.

Please make your question more descriptive about the schema of your database and query you are making for api.

Why does this simple WHERE clause doesn't return any data?

You should use JSON_VALUE instead of JSON_QUERY to extract a value as a BigQuery String. JSON QUERY returns a value as JSON-compatible String, not BigQuery String.

For example,

SELECT JSON_QUERY('{ "email": "blabla@example.com" }', '$.email') email;

Note that returned value is wrapped with double quotes.

But if you use JSON_VALUE,

SELECT JSON_VALUE('{ "email": "blabla@example.com" }', '$.email') email;

You can get a value without double quotes and be able to compare it with other BigQuery Strings IN ("blabla@example.com", "blabla2@example.com"...)

---

Related Topics