JSON and unstructured data

PostgreSQL provides json and jsonb types for flexible data structures. In CloudBase PostgreSQL, prefer jsonb because it supports efficient queries, indexes, and field operations.

JSON fields are suitable for extensible configuration, third-party callback payloads, form drafts, and event contexts. Keep core business fields as normal columns so you can add constraints, indexes, and permission checks.

json and jsonb

Type	Characteristics	Recommendation
json	Preserves original text format and has lower write overhead	Use when internal fields are rarely queried
jsonb	Binary storage with indexes and efficient queries	Preferred by default

Create JSON fields

create table public.orders (
  id bigint generated by default as identity primary key,
  user_id varchar(64) not null default auth.uid(),
  status text not null default 'pending',
  metadata jsonb not null default '{}',
  created_at timestamptz not null default now()
);

Insert JSON data by passing a JSON object.

insert into public.orders (status, metadata)
values (
  'paid',
  '{
    "channel": "wechat",
    "coupon": "new-user",
    "client": { "platform": "mp" }
  }'::jsonb
);

Query JSON fields

Common operators:

Operator	Description
->	Returns a JSON object or array
->>	Returns a text value
@>	Checks whether JSON contains a value
?	Checks whether a top-level key exists

select id, metadata ->> 'channel' as channel
from public.orders
where metadata ->> 'channel' = 'wechat';

Containment queries check whether a JSON fragment exists.

select id
from public.orders
where metadata @> '{"coupon": "new-user"}'::jsonb;

Update JSON fields

Use jsonb_set to update a specific path.

update public.orders
set metadata = jsonb_set(metadata, '{client,version}', '"1.2.0"', true)
where id = 1;

Use the - operator to remove a field.

update public.orders
set metadata = metadata - 'coupon'
where id = 1;

HTTP API queries

The HTTP API can use PostgREST-compatible JSON path filters. Use the HTTP API reference as the source of truth for supported syntax.

curl -X GET 'https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/orders?metadata->>channel=eq.wechat' \
  -H 'Authorization: Bearer <access_token>'

Index optimization

For frequent @> containment queries, create a GIN index.

create index orders_metadata_gin_idx
on public.orders
using gin (metadata);

For frequent filtering on a JSON subfield, create an expression index.

create index orders_channel_idx
on public.orders ((metadata ->> 'channel'));

Modeling recommendations

• Use normal columns for fields that are frequently queried, sorted, constrained, or used in permission checks.
• Use jsonb for fast-changing, sparse, display-only, or audit fields.
• Do not put the entire business model into one JSON field. It makes queries, permissions, and migrations harder later.