Json Methods

Along side the ability to traverse and work with JSON documents using the Laravel style arrow selectors. We also have a collection of JSON helper methods, these allow for more complex functionality, while increasing readability.

if you wish to access these methods, please call jsonBuilder() before defining your query.

v0.2.0 — JSON Support Phase 2 added a set of JSON modification expressions (JSON_SET, JSON_INSERT, JSON_MERGE_*, etc.) accessed via jsonExpression(), plus a cast-to-type option on orderByJson(). See JSON Modification (Phase 2) below. All JSON helpers use the portable verbose JSON_* function syntax (never ->/->>) so they run on MySQL 5.7+ and MariaDB 10.2+.

selectJson()

It is possible to select single values (either inner object/array or just values) from within JSON documents.

/**
 * @param string|Raw           $column   The Database column holding JSON document  
 * @param string|string[]|Raw  $nodes       Either single node, array of nodes or raw expression  
 * @param string               $alias       Sets the 'AS Alias'   
 * @return QueryBuildHandler
 */
public function selectJson($column, $nodes, $alias): QueryBuilderHandler

DB Table

id	title	authorData
1	5 Easy Rules Of Tree	{"data":{"author_id":12, "author_name":"Sam"}}
2	The Death Of Lard	{"data":{"author_id":42, "author_name":"James"}}
3	Is Bacon Still Relevant?	{"data":{"author_id":12, "author_name":"Sam"}}
4	The Miracle Of Sponge	{"data":{"author_id":24, "author_name":"Jane"}}

Usage

$builder->jsonBuilder()
    ->table('foo')
    ->select('id', 'title')
    ->selectJson('authorData',['data','author_name'], 'author')
    ->get()

  0 => {id: 1, title: "5 Easy Rules Of Tree", author: "Sam"},
  1 => {id: 2, title: "The Death Of Lard", author: "James"},
  2 => {id: 3, title: "Is Bacon Still Relevant?", author: "Sam"},
  3 => {id: 4, title: "The Miracle Of Sponge", author: "Jane"}

whereJson()

Also includes orWhereJson()

It is possible to select single values (either inner object/array or just values) from within JSON documents.

whereJson() - joins multiple Where Statements with 'AND'
orWhereJson() - joins multiple Where Statements with 'OR'

/**
 * @param string|Raw          $column   The database column which holds the JSON value
 * @param string|string[]|Raw $nodes    Either single node, array of nodes or raw expression
 * @param string|mixed|null   $operator Can be used as value, if 3rd arg not passed
 * @param mixed|null          $value    The value to match to the JSON value
 * @return QueryBuilderHandler
 */
public function whereJson($column, $nodes, $operator, $value ): QueryBuilderHandler

It is possible to omit the $operator value and it assumed as =

DB Table

id	title	authorData
1	5 Easy Rules Of Tree	{"data":{"id":12, "name":"Sam"}}
2	The Death Of Lard	{"data":{"id":42, "name":"James"}}
3	Is Bacon Still Relevant?	{"data":{"id":12, "name":"Sam"}}
4	The Miracle Of Sponge	{"data":{"id":24, "name":"Jane"}}

Usage

$builder->jsonBuilder()
    ->table('foo')
    ->whereJson('authorData',['data','id'], 12)
    ->orWhereJson('authorData',['data','id'], 24)
    ->get()

Results

  0 => {id: 1, title: "5 Easy Rules Of Tree", authorData: {"data":{"id":12, "name":"Sam"}}},
  1 => {id: 3, title: "Is Bacon Still Relevant?", authorData: {"data":{"id":12, "name":"Sam"}}}
  2 => {id: 4, title: "The Miracle Of Sponge", authorData: {"data":{"id":24, "name":"Jane"}}}

whereNotJson()

Also includes orWhereNotJson()

It is possible to create a `Where value inside JSON document doesn't match the operator and value defined.

whereNotJson() - joins multiple Where Statements with 'AND'
orWhereNotJson() - joins multiple Where Statements with 'OR'

/**
 * @param string|Raw          $column   The database column which holds the JSON value
 * @param string|string[]|Raw $nodes    Either single node, array of nodes or raw expression
 * @param string|mixed|null   $operator Can be used as value, if 3rd arg not passed
 * @param mixed|null          $value    The value to match to the JSON value
 * @return QueryBuilderHandler
 */
public function whereNotJson($column, $nodes, $operator, $value ): QueryBuilderHandler

It is possible to omit the $operator value and it assumed as =

DB Table

id	title	authorData
1	5 Easy Rules Of Tree	{"data":{"id":12, "name":"Sam"}}
2	The Death Of Lard	{"data":{"id":42, "name":"James"}}
3	Is Bacon Still Relevant?	{"data":{"id":12, "name":"Sam"}}
4	The Miracle Of Sponge	{"data":{"id":24, "name":"Jane"}}

Usage

$builder->jsonBuilder()
    ->table('foo')
    ->whereNotJson('authorData',['data','id'], 24)
    ->orWhereNotJson('authorData',['data','id'], 42)
    ->get()

Results

  0 => {id: 1, title: "5 Easy Rules Of Tree", authorData: {"data":{"id":12, "name":"Sam"}}},
  1 => {id: 3, title: "Is Bacon Still Relevant?", authorData: {"data":{"id":12, "name":"Sam"}}}

whereInJson()

Also includes orWhereInJson()

It is possible to create a `Where value inside JSON document doesn't match the operator and value defined.

whereInJson() - joins multiple WhereInJson Statements with 'AND'
orWhereInJson() - joins multiple WhereInJson Statements with 'OR'

/**
 * @param string|Raw          $column    The database column which holds the JSON value
 * @param string|string[]|Raw $nodes     Either single node, array of nodes or raw expression
 * @param mixed[]             $values    Array of values to look for a match in
 * @return QueryBuilderHandler
 */
public function whereInJson($column, $nodes, $values ): QueryBuilderHandler

DB Table

id	name	choices
1	Glynn	{"data":{"colour":"red", "flavour":"apple"}}
2	Oliver	{"data":{"colour":"red", "flavour":"berry"}}
3	Dexter	{"data":{"colour":"green", "flavour":"cream"}}
4	Mary	{"data":{"colour":"blue", "flavour":"bacon"}}

Usage

$builder->jsonBuilder()
    ->table('foo')
    ->whereInJson('choices',['data','colour'], ['green', 'yellow'])
    ->orWhereInJson('choices',['data','flavour'], ['bacon', 'cheese', 'pear'])
    ->get()

Results

  0 => {id: 3, name: "Dexter", choices: {"data":{"colour":"green", "flavour":"cream"}}},
  1 => {id: 4, name: "Mary", choices: {"data":{"colour":"blue", "flavour":"bacon"}}}

❗ Don't forget you can use bindings to help avoid XSS injection

->whereJson('column', ['json','object'], [Bindings::asString($variable1), Bindings::asString($variable2)]);

whereNotInJson()

Also includes orWhereNotInJson()

It is possible to create a `Where value inside JSON document doesn't match the operator and value defined.

whereNotInJson() - joins multiple WhereNotInJson Statements with 'AND'
orWhereNotInJson() - joins multiple WhereNotInJson Statements with 'OR'

/**
 * @param string|Raw          $column    The database column which holds the JSON value
 * @param string|string[]|Raw $nodes     Either single node, array of nodes or raw expression
 * @param mixed[]             $values    Array of values to look for a match NOT in
 * @return QueryBuilderHandler
 */
public function whereNotInJson($column, $nodes, $values ): QueryBuilderHandler

DB Table

id	name	choices
1	Glynn	{"data":{"colour":"red", "flavour":"apple"}}
2	Oliver	{"data":{"colour":"red", "flavour":"berry"}}
3	Dexter	{"data":{"colour":"green", "flavour":"cream"}}
4	Mary	{"data":{"colour":"blue", "flavour":"bacon"}}

Usage

$builder->jsonBuilder()
    ->table('foo')
    ->whereNotInJson('choices',['data','colour'], ['green', 'yellow'])
    ->orWhereNotInJson('choices',['data','flavour'], ['bacon', 'cheese', 'pear'])
    ->get()

Results

  0 => {id: 1, name: "Glynn", choices: {"colour":"red", "flavour":"apple"}}},
  1 => {id: 2, name: "Oliver", choices: {"data":{"colour":"red", "flavour":"berry"}}}

whereBetweenJson()

Also includes orWhereBetweenJson()

It is possible to create a `Where value inside JSON document doesn't match the operator and value defined.

whereBetweenJson() - joins multiple whereBetweenJson Statements with 'AND'
orWhereBetweenJson() - joins multiple whereBetweenJson Statements with 'OR'

/**
 * @param string|Raw          $column    The database column which holds the JSON value
 * @param string|string[]|Raw $nodes     Either single node, array of nodes or raw expression
 * @param int|float|Raw       $valueFrom The min value
 * @param int|float|Raw       $valueTo   The max value
 * @return QueryBuilderHandler
 */
public function whereBetweenJson($column, $nodes, $valueFrom, $valueTo ): QueryBuilderHandler

DB Table

id	name	results
1	Glynn	{"data":{"step1":2, "step2":8}}
2	Oliver	{"data":{"step1":4, "step2":3}}
3	Dexter	{"data":{"step1":3, "step2":2}}
4	Mary	{"data":{"step1":9, "step2":7}}

Usage

$builder->jsonBuilder()
    ->table('foo')
    ->whereBetweenJson('choices',['data','step1'], 7, 9)
    ->orWhereBetweenJson('choices',['data','step2'], 7, 9)
    ->get()

Results

  0 => {id: 1, name: "Glynn", results: {"data":{"step1":2, "step2":8}}},
  1 => {id: 4, name: "Mary", results:  {"data":{"step1":9, "step2":7}}}

orderByJson()

It is possible to order a query by a single or multiple values. The basic arrow selectors will not do any casting of the data value and treat as a basic string value. This can be controlled using the following flags.

/**
 * @param string|Raw           $column   The Database column holding JSON document  
 * @param string|string[]|Raw  $nodes       Either single node, array of nodes or raw expression  
 * @param string               $direction   Direction 'ASC' or 'DESC'.   
 * @param string|null          $cast        (Since v0.2.0) Optional SQL type to CAST the extracted value to, e.g. 'UNSIGNED' or 'DECIMAL(10,2)'. Null = no cast.
 * @return QueryBuildHandler
 */
public function orderByJson($column, $nodes, $direction='ASC', $cast=null): QueryBuilderHandler

Since v0.2.0 — the extracted JSON value is unquoted text by default, so numeric values sort lexicographically ("10" before "9"). Pass the $cast argument to sort by the real type:
$builder->jsonBuilder()
    ->table('foo')
    ->orderByJson('metaData', ['stats','likes'], 'DESC', 'UNSIGNED')
    ->get();
// ORDER BY CAST(JSON_UNQUOTE(JSON_EXTRACT(metaData, "$.stats.likes")) AS UNSIGNED) DESC

DB Table

id	title	metaData
1	Some Tile	{"stats":{"likes":450, "dislikes":5}}
2	Foo Bar	{"stats":{"likes":45, "dislikes":500}}
3	Words	{"stats":{"likes":85463, "dislikes":785}}
4	Examples	{"stats":{"likes":45, "dislikes":14}}
Usage

$builder->jsonBuilder()
    ->table('foo')
    ->orderByJson('metaData',['stats','likes'], 'DESC')
    ->orderByJson('metaData',['stats','dislikes'], 'ASC')
    ->get()

Results

  0 => {id: 3, string: "Words", metaData:{stats:{likes:85463, dislikes:785}}},
  1 => {id: 1, string: "Some Tile", metaData: {stats:{likes:450,dislikes:5}}},
  2 => {id: 4, string: "Examples", metaData: {stats:{likes:45,dislikes:14}}}},
  3 => {id: 2, string: "Foo Bar",  metaData: {stats:{likes:45,dislikes:500}}}

JSON Modification (Phase 2)

Since v0.2.0

Phase 2 adds a set of expressions for modifying a JSON document. They are built from jsonExpression() and return a Raw expression, so they compose into update(), insert(), select() and orderBy() like any other expression. All use the portable verbose JSON_* syntax (MySQL 5.7+ / MariaDB 10.2+).

Method	Generates	Notes
`set($column, $nodes, $value)`	`JSON_SET`	Insert or update the value at the path
`insert($column, $nodes, $value)`	`JSON_INSERT`	Only if the path does not exist
`replace($column, $nodes, $value)`	`JSON_REPLACE`	Only if the path does exist
`appendArray($column, $nodes, $value)`	`JSON_ARRAY_APPEND`	Append a value to an array
`insertArray($column, $nodes, $value)`	`JSON_ARRAY_INSERT`	Insert at an index, e.g. `['list[1]']`
`remove($column, $nodes)`	`JSON_REMOVE`	Remove the value at the path
`merge($column, $doc)` / `mergePreserve($column, $doc)`	`JSON_MERGE_PRESERVE`	Merge, preserving duplicate keys as arrays
`mergePatch($column, $doc)`	`JSON_MERGE_PATCH`	RFC 7396 merge (last value wins)

Scalar values are inlined (strings quoted/escaped, numbers/bools raw, null as NULL), and array/object values + merge documents are inlined portably as JSON_EXTRACT('<json>', '$') (which works on both MySQL and MariaDB — CAST(... AS JSON) is rejected by MariaDB).

Usage

$qb = $connection->getQueryBuilder(); // or your JsonQueryBuilder instance

// Update a nested value
$qb->table('settings')
    ->where('id', '=', 1)
    ->update(['data' => $qb->jsonExpression()->set('data', ['profile', 'name'], 'Sam')]);
// UPDATE settings SET data = JSON_SET(data, "$.profile.name", 'Sam') WHERE id = 1

// Append to a JSON array
$qb->jsonExpression()->appendArray('data', ['tags'], 'php');
// JSON_ARRAY_APPEND(data, "$.tags", 'php')

// Merge a document (RFC 7396)
$qb->jsonExpression()->mergePatch('data', ['active' => true]);
// JSON_MERGE_PATCH(data, JSON_EXTRACT('{"active":true}', '$'))

// Remove a key
$qb->jsonExpression()->remove('data', ['legacy']);
// JSON_REMOVE(data, "$.legacy")

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Json Methods

selectJson()

whereJson()

whereNotJson()

whereInJson()

whereNotInJson()

whereBetweenJson()

orderByJson()

JSON Modification (Phase 2)

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally