Two operators on accesing property in jsonb in PostgreSQL

In PostgreSQL, the jsonb data type allows you to store and manipulate JSON data. When working with jsonb columns, you can use the -> and ->> operators to extract values from JSON objects.

Here are the differences between these two JSON object access operators:

1. ->

• This operator returns the value as jsonb.
• It extracts the value of a specified property from the JSON object and returns it as a JSON object (in jsonb format).
• You can further access properties within the returned JSON object using additional -> operators.

2. ->>

• This operator returns the value as text.
• It extracts the value of a specified property from the JSON object and returns it as text.
• The returned value is not in JSON format but as a plain text representation of the value.

To illustrate the difference, consider the following example:

Suppose you have a jsonb column named data with the following JSON object stored in it:

{
  "name": "John",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "New York"
  }
}

Using the -> operator:

• data->'name' would return {“name”: “John”} (as jsonb).
• data->'name'->>'name' would return “John” (as text).

Using the ->> operator:

• data->>'name' would directly return “John” (as text).

So, the -> operator returns a JSON object, while the ->> operator returns the value as text without the surrounding JSON object structure.

Choose the appropriate operator based on whether you need the value as jsonb or text in your specific use case.