CODEWITHSUNDEEP
postgresqlpostgresql-json
suprita shankar
suprita shankar

Reputation: 1589

JSON vs TEXT field to keep a list of values in Postgres

What is the best way to keep the following on Postgres?

[{'property':'foo1', 'val': "foo_val"}, {'property': 'foo2', 'val': "foo_val2"}]

JSON or Text?

From what I understand JSON is good for nested structure(using ->>). But in this case, its a flat structure. Would it still make sense to keep it as JSON or TEXT. Which will be more easier to query?

Upvotes: 3

Views: 4219

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

I would go with JSON for non-nested structures anyway. Postgres supports operators to get, compare, delete key/value pairs or entire objects. Please also read further about JSONB as it might be better for your use case, especially if you plan on doing database operations on json value and would also like to index specific keys.

You can't simply write a query to match value of a key when you store json as TEXT, but you can do that using built in json operators and functions, and there's many more things you can do with it. As an example you can't retrieve foo_val2 as a value of some key, because string does not have key-value pairs, it's all text.

What I've learned the hard way is that there's no optimization for containment operator for example LIKE, so you will still need to unpack the json to text and then perform a search - see my question on StackOverflow regarding this topic.

Summing up, JSON or JSONB should be your first choice when dealing with json data because it allows you flexibility you don't have with TEXT. Especially if you're planning on doing some work on the database side, or if you will find yourself willing to do so.

Upvotes: 3

Related Questions