view release on metacpan or  search on metacpan

docs/adr/0005-native-jsonb-operators-and-jsonb-path-dsl.md  view on Meta::CPAN

JSONB querying is a headline PostgreSQL capability; expressing it through
`expand_op` (rather than reviving the legacy special-op API) is exactly what
core ADR 0004 asks every driver to do, so the PostgreSQL operators slot into the
same predictable operator model as the rest of the family. The `?`-to-function
rewrite is not cosmetic: it is the only way to use the key-existence operators at
all through a DBI placeholder-based layer — `jsonb_exists*()` are PostgreSQL's
own functional equivalents, so the rewrite is faithful, not a hack around
semantics. The `jsonb()` DSL is split out from the operator handlers because
path extraction returns a *value* to compare with standard operators, whereas
`@>` / `?` are whole-value predicates; keeping them in separate modules
(`SQLMaker` transparent in `search`, `JSONB` an explicit import) matches how a
user reaches for each.

## Consequences

- Containment, jsonpath and key-existence operators work transparently in
  `search({ 'me.data' => { '@>' => {...} } })` with no import, because they are
  registered on the SQLMaker the storage instantiates.
- Field comparison requires `use DBIO::PostgreSQL::JSONB qw(jsonb)` — a
  deliberate, explicit dependency for the DSL, distinct from the
  always-available operators.
- The operator set is coupled to `SQL::Abstract`'s `expand_op` contract (core
  ADR 0002 / 0004); an engine-internal change to how `expand_op` handlers are
  invoked would land here and must be regression-tested against the JSONB
  operators.
- The `?`-family will always render as `jsonb_exists*()` functions, never as the

lib/DBIO/PostgreSQL/JSONB.pm  view on Meta::CPAN

Provides the C<jsonb()> helper function that builds text-extraction path
expressions for PostgreSQL JSONB columns. The generated SQL uses C<<< ->> >>>
(single-level) or C<<< #>> >>> (multi-level) to extract a text value, which
can then be compared with standard SQL operators.

This module covers the path-extraction side of JSONB querying. The comparison
methods themselves live on L<DBIO::PostgreSQL::JSONB::Op>, the operator object
returned by C<jsonb()>.

For containment and key-existence operators (C<<< @> >>>, C<?>, etc.) see
L<DBIO::PostgreSQL::SQLMaker>, which handles those transparently in
C<search()> without any extra import.

=head1 METHODS

=head2 jsonb

  use DBIO::PostgreSQL::JSONB qw(jsonb);

  my $expr = jsonb($column, @path);

lib/DBIO/PostgreSQL/SQLMaker.pm  view on Meta::CPAN

=head1 VERSION

version 0.900000

=head1 DESCRIPTION

L<DBIO::SQLMaker> subclass for PostgreSQL. Extends standard SQL generation
with native support for PostgreSQL JSONB operators.

Used automatically by L<DBIO::PostgreSQL::Storage> — the C<sql_maker_class>
is set on the storage and this class is instantiated transparently whenever a
PostgreSQL connection is opened.

=head2 Containment: C<@>> and C<<@>

Tests whether a JSONB value contains (or is contained by) another JSONB
value. The RHS hashref or arrayref is JSON-encoded automatically.

  $rs->search({ 'me.data' => { '@>' => { status => 'active' } } });
  # WHERE "me"."data" @> '{"status":"active"}'::jsonb