Laravel Eloquent JSON Macros

This package helps you to use MySQL JSON functions in Eloquent style and as helper functions.

Installation

You can install the package using composer

$ composer require aidynmakhataev/eloquent-json-macros

Features

• Support macros:

  • jsonContains
  • orJsonContains
  • whereJsonContainsPath
  • orWhereJsonContainsPath
  • whereJsonDepth
  • orWhereJsonDepth
  • whereJsonExtract
  • orWhereJsonExtract
  • whereJsonLength
  • orWhereJsonLength
  • selectJsonDepth
  • selectJsonLength

• Available helpers:

  • json_array_append
  • json_array_insert
  • json_insert
  • json_remove
  • json_replace
  • json_set

Usage

Let's say we have a table events with json columns - browser and members;

Browser (dummy json object)

{"os": "Windows", "name": "Safari", "resolution": {"x": 1920, "y": 1080}}  

Members (dummy json array)

[{"id": 6, "info": {"job": "Electrolytic Plating Machine Operator", "email": "[email protected]", "card_type": "Visa"}, "name": "Prof. Eldridge Legros"}, {"id": 8, "info": {"job": "Urban Planner", "email": "[email protected]", "card_type": "Master Card"}, "name": "Ms. Alayna Ziemann DDS"}]

jsonContains

Add where 'JSON_CONTAINS' clause to the query for indicates whether JSON document contains specific object at path

More on: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains

Example (for browserjson object column)

use App\Models\Event;

Event::jsonContains('browser->os', 'Windows')->get();
Event::jsonContains('browser->resolution.x', 1920)->get();

Example (for memberjson array column)

use App\Models\Event;

Event::jsonContains('members->[*].id', 6)->get();
Event::jsonContains('members->[1].info.email', '[email protected]')->get();

orJsonContains

Add an orWhere 'JSON_CONTAINS' clause to the query for indicates whether JSON document contains specific object at path

Usage will be same as in jsonContains macro;

whereJsonContainsPath

Add a where 'JSON_CONTAINS_PATH' clause to the query for indicates whether JSON document contains any data at path

More on: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-contains-path

Example (for browserjson object column)

For single path

Event::whereJsonContainsPath('browser', 'resolution')->get();

For multiple path

Event::whereJsonContainsPath('browser', ['resolution', 'os'])->get();

You can also optionally pass a third parameter ('one' or 'all'), by default used 'one'

Event::whereJsonContainsPath('browser', ['resolution', 'test'], 'all')->get();

Example (for memberjson array column)

Event::whereJsonContainsPath('members', '[*].info')->get();
Event::whereJsonContainsPath('members', ['[*].info', '[1].test'])->get();

orWhereJsonContainsPath

Add an orWhere 'JSON_CONTAINS_PATH' clause to the query for indicates whether JSON document contains any data at path

Usage will be same as in whereJsonContainsPath macro;

whereJsonDepth

Add a where 'JSON_DEPTH' clause to the query for indicates depth of JSON document

More on: https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-depth

Example (for browserjson object column)

Event::whereJsonDepth('browser->resolution', '>', 1)->get();
Event::whereJsonDepth('browser->os',  2)->get();
Event::whereJsonDepth('browser->resolution.x',  '<=', 1)->get();

Example (for memberjson array column)

Event::whereJsonDepth('members->[*].info.job', '>=', 1)->get();
Event::whereJsonDepth('members->[1].info.phones[0].fax', '>', 0)->get();

orWhereJsonDepth

Add an orWhere 'JSON_DEPTH' clause to the query for indicates depth of JSON document

Usage will be same as in whereJsonDepth macro;

whereJsonExtract

Add a where "JSON_EXTRACT" clause to the query.

More on: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract

Example (for browserjson object column)

Event::whereJsonExtract('browser->resolution.x', '>', 1500)->get();
Event::whereJsonExtract('browser->name', 'Mozilla Firefox')->get();

Example (for memberjson array column)

Event::whereJsonExtract('members->[0].id', '>=', 9)->get();
Event::whereJsonExtract('members->[*].info.job', 'LIKE', '%Cleaners%')->get();

orWhereJsonExtract

Add an orWhere "JSON_EXTRACT" clause to the query.

Usage will be same as in whereJsonExtract macro;

whereJsonLength

Add a where 'JSON_LENGTH' clause to the query.

More on: https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html#function_json-length

Example (for browserjson object column)

Event::whereJsonLength('browser->resolution', '>', 1)->get();
Event::whereJsonLength('browser->os',  4)->get();
Event::whereJsonLength('browser->resolution.x',  '>=', 1)->get();

Example (for memberjson array column)

Event::whereJsonLength('members->[*]', '>=', 1)->get();
Event::whereJsonLength('members->[1].info.phones[*].fax', '>', 0)->get();

orWhereJsonLength

Add an orWhere 'JSON_LENGTH' clause to the query

Usage will be same as in whereJsonLength macro;

TODO EXPLANATION FOR OTHER MACROS AND HELPERS

Contributing

1. Fork it (https://github.com/AidynMakhataev/eloquent-json-macros/fork)
2. Create your feature branch (git checkout -b feature/fooBar)
3. Commit your changes (git commit -am 'Add some fooBar')
4. Push to the branch (git push origin feature/fooBar)
5. Create a new Pull Request

Security

If you discover any security related issues, please email [email protected] instead of using the issue tracker.

License

MIT