PostgreSQL Query Example with JSON and Common Table Expression

Published on July 3, 2025

A interesting query:

WITH extracted_data AS (
  SELECT
    created,
    (m.assistant_message::json)->'choices'->0->>'text' AS assistant_response
  FROM
    public.message AS m
  WHERE
    m.user_message IS NULL
)

SELECT
  *
FROM
  extracted_data
WHERE
  assistant_response ~ '\[.*\]'
ORDER BY
  created DESC;

The first part is a common table expression, which is like a temporary table to split the query into two phases.

To get a json object, use -> to get text, use ->> . As assistant_message is a TEXT, we need to cast it to json using ::json.