How to Query JSON and JSONB Data in PostgreSQL?

Quering JSON Datatype Fields in PostgreSQL

PostgreSQL 9.2 introduced the JSON datatype, but advanced querying of fields within JSON records required custom functions. PostgreSQL 9.3 expanded these capabilities with operators and functions.

Specifically, for a table with a JSON column named "data":

Postgres 9.3:

• To find a record by name:

SELECT object
FROM   json_tbl
     , json_array_elements(data) AS object
WHERE  object->>'name' = 'Toby';

Postgres 9.4:

• Introduced the jsonb datatype, which stores JSON data in native Postgres types.
• Indexes using GIN or other types can improve performance.

Postgres 9.5:

• Complete jsonb functions and operators.
• More functions for manipulating and displaying jsonb data.

Postgres 12:

• SQL/JSON path language support
• To find a record by name using JSON path:

SELECT jsonb_path_query_first(data, '$[*] ? (@.name == "Toby")') AS object
FROM   jsonb_tbl
WHERE  data @> '[{ "name": "Toby"}]';  -- optional for indexing

• Or equivalent:

  ...
  WHERE  data @@ '$[*].name == "Toby"';

Additional resources:

• [Postgres 9.3 JSON Functionality](https://www.postgresql.org/docs/9.3/static/)
• [Postgres JSONB Datatype](https://www.postgresql.org/docs/current/static/datatype-json.html)
• [Postgres 12 JSON Path Support](https://www.postgresql.org/docs/12/static/functions-json.html)
• [Github PostSQL Functions](https://github.com/daviddias/postsด้วย)

The above is the detailed content of How to Query JSON and JSONB Data in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!