1

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?

4
  • 4
    [1,2,3] is valid JSON. Commented Jul 3, 2018 at 1:06
  • i thought it needed enclosing { }, must be for http requests only Commented Jul 3, 2018 at 1:08
  • 2
    {} is for JSON objects, [] is for JSON arrays. Those are 2 distinct JSON types. Commented Jul 3, 2018 at 1:10
  • If the only thing you store is an array of integers, an int[] would be the better choice. Commented Jul 4, 2018 at 7:03

2 Answers 2

3

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).

Sign up to request clarification or add additional context in comments.

Comments

0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.