How to parse JSON in postgresql

Parsing JSON data in PostgreSQL typically involves two data types: json and jsonb. jsonb is the binary format of the json type, supporting indexing, and query and operation performance are typically better than with the json type. Here are several methods to parse JSON data in PostgreSQL:

1. Using -> and ->> Operators

These operators are used to retrieve data from JSON. The -> operator returns a JSON object or array (depending on the path), while ->> returns text.

Example:

Assume a table named data with an info field of type jsonb, containing the following:

json
{
  "name": "张三",
  "age": 28,
  "contact": {
    "email": "zhangsan@example.com",
    "phone": "1234567890"
  }
}

To retrieve the name, use:

sql
SELECT info ->> 'name' AS name FROM data;

To retrieve contact information, use:

sql
SELECT info -> 'contact' AS contact FROM data;

2. Using jsonb_array_elements and jsonb_each

When the JSON field is an array or object, these functions can be used to expand arrays or objects.

Example:

If info also contains a skills array:

json
{
  "skills": ["SQL", "Python", "Java"]
}

To expand the skills array, use:

sql
SELECT jsonb_array_elements_text(info -> 'skills') AS skill FROM data;

3. Using jsonb_populate_record

When you have a complex type in PostgreSQL and a JSON object, and you want to convert the JSON object to that complex type, you can use jsonb_populate_record.

Example:

Create a type representing a user:

sql
CREATE TYPE user_type AS (
  name text,
  age int,
  email text,
  phone text
);

Convert the info field to user_type:

sql
SELECT * FROM jsonb_populate_record(null::user_type, info) AS user;

4. Using Indexes to Optimize Queries

Creating an index on the jsonb field can optimize query performance.

Example:

Create a GIN index on the name of info:

sql
CREATE INDEX idx_gin_info ON data USING gin ((info -> 'name'));

These basic methods and features make handling JSON data in PostgreSQL flexible and powerful. Depending on the specific application scenario and requirements, choose the most suitable method to parse and operate on JSON data.