CODEWITHSUNDEEP
postgresql
el_pup_le
el_pup_le

Reputation: 12179

Why use Postgres JSON column type?

The JSON column type accepts non valid JSON

eg [1,2,3] can be inserted without the closing {}

Is there any difference between JSON and string?

Upvotes: 1

Views: 2374

Answers (2)

Garima Aggarwal
Garima Aggarwal

Reputation: 21

The above data can be stored in text, but the JSON data types have the advantage you can apply JSON rules in those columns. There are several functions which are JSON specified which cannot be used for text fields.

Refer to this link to understand about the json functions/procedures

Upvotes: 0

khampson
khampson

Reputation: 15296

While [1,2,3] is valid JSON, as zerkms has stated in the comments, to answer the primary question: Is there any difference between JSON and string?

The answer is yes. A whole new set of query operations, functions, etc. apply to json or jsonb columns that do not apply to text (or related types) columns.

For example, while with text columns you would need to use regular expressions and related string functions to parse the string (or a custom function), with json or jsonb, there exists a separate set of query operators that works within the structured nature of JSON.

From the Postgres doc, given the following JSON:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

The doc then says:

We store these documents in a table named api, in a jsonb column named jdoc. If a GIN index is created on this column, queries like the following can make use of the index:

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

This allows you to query the jsonb (or json) fields very differently than if it were simply a text or related field.

Here is some Postgres doc that provides some of those query operators and functions.

Basically, if you have JSON data that you want to treat as JSON data, then a column is best specified as json or jsonb (which one you choose depends on whether you want to store it as plain text or binary, respectively).

Upvotes: 3

Related Questions