NAME
DBIO::SQLite::JSON - JSON path expression helpers for DBIO::SQLite queries
VERSION
version 0.900007
SYNOPSIS
use DBIO::SQLite::JSON qw(json);
# Equality on an extracted text value
$rs->search( json('me.data')->get_text('$.status')->eq('active') );
# WHERE (me.data ->> ?) = ? bind: '$.status', 'active'
# The portable json_extract() function form
$rs->search( json('me.data')->extract('$.status')->eq('active') );
# WHERE (json_extract(me.data, ?)) = ?
# The -> operator, yielding a JSON value
$rs->search( json('me.data')->get('$.meta')->is_not_null );
# WHERE (me.data -> ?) IS NOT NULL
# Numeric comparisons, NULL checks, LIKE
$rs->search( json('me.stats')->get_text('$.score')->gt(100) );
$rs->search( json('me.data')->get_text('$.avatar')->is_null );
$rs->search( json('me.data')->get_text('$.name')->like('John%') );
# ORDER BY / SELECT
$rs->search( {}, { order_by => json('me.data')->get_text('$.total')->as_order } );
$rs->search( {}, { select => [ json('me.data')->extract('$.status')->as_select('status') ] } );
DESCRIPTION
Provides the json() helper that builds JSON path-extraction expressions for SQLite columns. It exposes the three SQLite extraction forms:
json_extract(col, path)— the portable function (JSON1 extension, SQLite 3.9+), via "extract" in DBIO::SQLite::JSON::Op.col -> path— the->operator (SQLite 3.38+), yielding a JSON value, via "get" in DBIO::SQLite::JSON::Op.col ->> path— the->>operator (SQLite 3.38+), yielding a text / SQL value, via "get_text" in DBIO::SQLite::JSON::Op.
In every form the path is bound as a placeholder, so it is never a SQL-injection surface. The comparison methods on the returned operator object (DBIO::SQLite::JSON::Op) turn an extraction into a condition fragment suitable for search().
METHODS
json
use DBIO::SQLite::JSON qw(json);
my $expr = json($column);
Returns a DBIO::SQLite::JSON::Op object bound to $column. Chain a selector method on it to choose the extraction form and the JSON path, then a comparison or consumption method to produce a fragment for search():
json('me.data')->extract('$.status') # json_extract(me.data, ?)
json('me.data')->get('$.status') # me.data -> ? (JSON value)
json('me.data')->get_text('$.status') # me.data ->> ? (text/SQL value)
$column may include a table alias prefix (e.g. 'me.data') and is emitted verbatim — pass an identifier, not user input. The JSON path always travels as a placeholder bind, never interpolated into the SQL.
SEE ALSO
DBIO::SQLite::JSON::Op — operator object returned by
json(), with the selector and comparison methodsDBIO::SQLite::SQLMaker — SQLite SQL generation
DBIO::SQLite::Storage — SQLite storage layer
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.