Database PostgreSql: column String or JSON?

Question

i have a project write in Java 17. We use spring-boot and database PostgreSql. We have a table called "Setting" with this column: Id BIGINT, KEY String, VALUE String, uuid UUID

In this table we save generic configuration of project. For example:

KEY=workingDir, VALUE="C:\workingDir" KEY=encoding, VALUE="UTF8" ecc...

But now we need to save a KEY that VALUE is a JSON. Example:

KEY=pattern, VALUE= {range:10-50,valid:2,notvalid:0}

What are the best practises? Save JSON as a string? Other ideas? Thanks in advance

A piece of advice or an idea

Answer 1

Below are three options that might be worth considering:

1. json/jsonb
2. hstore
3. parent-child tree table

1: If the number of rows in the table named "Setting" will be significantly large (one reason people store values in databases) and you need to perform queries to find or sort the values, as of PostgreSQL v12 you may want to consider using either the jsonb or json PostgreSQL data types which provide:

• The advantage of enforcing that each stored value is valid according to the JSON rules.
• Assorted JSON-specific functions and operators available for data stored in these data types
• Certain limitations for jsonb type's data values but certain improved DB efficiencies (indexing as of v12) OR relaxed restrictions for json type's data and original data preservation but reduced DB efficiencies

This could be an additional column for JSON-specific strings or the original basic string values could be re-worked or normalized into JSON values (e.g. ["original value"] or {"value": "original value"}) depending on which setting value is more common or how you prefer to structure and manage your table.

2: The hstore module and data type is useful for storing sets of key/value pairs within a single PostgreSQL value but might be more limited compared to json or jsonb types, in part due to the flat nature of key/value pairs stored (no nesting).

3: Though unlikely to fit your needs/requirements, another alternative might be to avoid storing JSON entirely by enhancing your table with a parent + child relationship structure that would allow you to destructure the JSON into individual descendant entries/rows that relate to ancestor entry keys or ids.

Where {"range":[10, 50],"valid":2,"notvalid":0} stored as a string might be:

ID | KEY          | VALUE
1  | jsonSetting1 | {"range":[10, 50],"valid":2,"notvalid":0}
2  | strSetting1  | anySettingValue

Instead, a parent/child structure could be:

ID | PARENT | KEY           | VALUE
1  | null   | jsonSetting1  | null
2  | 1      | rangeMin      | 10
3  | 1      | rangeMax      | 50
4  | 1      | valid         | 2
5  | 1      | notValid      | 0
6  | null   | strSetting1   | anySettingValue

or a deeper ancestor hierarchy:

ID | PARENT | KEY           | VALUE
1  | null   | jsonSetting1  | null
2  | 1      | range         | null
3  | 2      | min           | 10
4  | 2      | max           | 50
5  | 1      | valid         | 2
6  | 1      | notValid      | 0
7  | null   | strSetting1   | anySettingValue

The parent/child relationship could be created via UUIDs as well.

Some drawbacks with this approach are added complexity to marshall/unmarshall the JSON, how SQL lookups are structured, or (generally) added SQL query complexity but it could be useful if there is a desire to run queries on individual values.