JSON Query Language (JQL - pronounced "jackal") is a query language system written in PHP for querying JSON strings and files. It also supports PHP stdClass objects. The "language" is heavily based on SQL, the popular database query language to allow easy adoption and quick learning, but it does have some limitations, simply because some of the advanced features of SQL are impossible to implement into such a system.

Before reading the complete documentation, please check out the minimal example to better understand how to use JQL.

1. Installation
2. Usage
   • Selecting Columns
   • Updating Columns
   • Searching Data
   • Sorting Results
   • Limiting Results
   • Executing the Query
3. Function Reference
   • String functions
   • Numeric functions
   • Date functions
   • Aggregate functions
   • update() Functions
4. Localization
5. Examples
6. Requirements
7. Roadmap
8. License

The easiest way to install the JQL library is using Composer. To add JQL to your project, simply run the following Composer command from your terminal:

$ composer require benmajor/jql

Alternatively, download all source files from the src/ directory of the repository and include them in your project.

JQL is designed to be intuitive for web developers to use; its syntax is loosely based upon SQL, and although the functionality is more limited, the results are as expected. The intention was to build a simple and efficient query language for JSON and PHP data structures. Usage is relatively simple and straightforward, but it is recommended to consult the examples in order to get the best results from JQL.

To get started, simply call the JQL constructor with your dataset. The constructor receives a single parameter, which can be any one of the following:

• JSON-encoded string
• String containing a file pointer
• A PHP array of objects

For example, all of the following constructors are valid:

Using a JSON-encoded string:

$jql = new JQL('[{
    'id':      2,
    'name':    'John',
    'surname': 'Doe'
}]');

Pointer to a valid JSON file:

$jql = new JQL('MyDataset.json');

PHP array of objects / associative arrays:

$jql = new JQL([
  [
    'id'      => 1,
    'name'    => 'John',
    'surname' => 'Doe'
  ]
]);

The constructor will return a new JQL object upon which it is possible to perform functions, alternatively, you can run SELECT-style queries on the data to return specific columns, and also order the results and limit them by a particular number and/or offset. The following sections will provide details on selecting, updating, ordering and limiting the data.

With the JQL object created, you should call the select() method to request particular columns from the JSON data.

Multiple fields should be specified in an array, but you can always request all fields by passing in a string containing * to the select() function as follows:

$jql->select( '*' );

Alternatively, you can specify specific columns to retrieve by passing them in as an array:

$jql->select([ 'name', 'surname' ]);

The above will return only the name and surname fields from each object in the specified dataset.

In addition to returning specific columns, JQL has built in functions that can be called on fields before selecting them. The majority of JQL's in-built functions are based upon SQL functions, and work in a similar way. Unfortunately, due to the complexity of SQL and the aim of this library to keep things fast and efficient, it is not possible to call multiple functions in one pass of the data.

Below is an example select() using two function calls:

$jql->select([
  'LCASE(name) AS name_lower',
  'CURRENT_TIMESTAMP() AS timestamp'
]);

For a full list of functions and examples, please see the Function Reference section of this documentation.

JQL is also able to return field aliases, and this can be achieved using the AS keyword. This is particularly handy if you need to reference a field by a shorter reference than its original column name. For example in the snippet above, name_lower is an alias field which does not exist in the original data structure. Aliases can be used without functions however, for example:

$jql->select([
  'super_long_column_name AS id'
]);

In addition to selecting fields, JQL is able to udpate fields in the original data structure as well. This is achieved by using the update() method, which is particularly useful when combined with the where() method (see details):

$jql->update([
  'name' => 'NAME REPLACED'
]);

JQL also has built-in functions that can be used when updating columns. Please see the Function Reference for more details about functions that can be used with the update() method.

The where() method works similarly to SQL's WHERE clause, and can be used to quickly filter the results for a select() or update() method.

The where() method accepts a single string of clauses, separated by either AND or OR. At the time of writing, JQL does not support combined clauses, such as AND and OR, only one or the other.

To filter the results for all records that have an age column value > 10, we can use:

$jql->select('forename')->where('age > 10');

JQL supports a number of operators for use with the where() method as follows:

JQL is able to order data based upon the column values specified. Multiple columns can be used for sorting, and the order can be specified for each column as follows:

$jql->select('forename')->order('surname', 'ASC');

The above snippet will order the results in ascending order by the value of the surname column. It is possible to order data by making multipl columns to order(), for example to sort by surname ascending and age descending, we can do the following:

$jql->select('forename')->order('surname', 'ASC')->order('age', 'DESC');

We can also limit results to a specific number using limit(), which can be particularly powerful when combined with offset(). For example, the following snippet will retrieve the first two matched records only:

$jql->select('forename')->where('age > 10')->limit(2);

It is also possible to specify the offset as a second parameter to the limit() method. For example, the following snippet will return 2 results, with the first result being skipped:

$jql->select('forename')->where('age > 10')->limit(2, 1);

You can alternatively specify the offset separately using the offset() method as follows (the following snippet is identical to the one shown above):

$jql->select('forename')->where('age > 10')->limit(2)->offset(1);

Once we have set up the query using the select() and update() methods, it's time to actually execute the query. This can be achieved using a number of functions in JQl, depending upon the result required:

fetch():
The fetch() method will return an array of all result matches, limited to the specified limit and offset, and sorted by the specified columns. If no matches are found, the function returns NULL.

fetchOne():
The fetchOne() method will return the first result from the set of matches. It will be returned as an object, not an array. If no matches are found, the function returns NULL.

fetchAsJSON( $prettyPrint = false ):
The fetchAsJSON method is the same as fetch(), except it returns a JSON-encoded string, rather than a PHP array. This function accepts a single parameter, which can be used to specify whether the returned JSON is pretty-printed or not -- must be a valid boolean (default is false).

fetchOneAsJSON():
As above, but for the first record.

saveAsFile( $pointer ):
Saves the resulting data to a JSON file located at $pointer.

count():
Simply returns an integer showing the number of affected rows.

All functions can be called on a column that appears in the JSON structure, or a fixed string (much like SQL). For example, the following both produce the same result:

$json = [ 
  [
    'id' => 1,
    'forename' => 'John',
    'surname' => 'Doe'
  ]
];

$jql = new JQL($json);

$jql->select([ 'UPPER(forename)' ]); # Returns JOHN for the first record.
$jql->select([ 'UPPER(John)' ]);     # Returns JOHN for ALL records.

The following functions can be used with the select() method:

The following functions can be used with the update() method:

• APPEND
• CONCAT_WS
• LCASE
• LEFT
• LOWER
• LPAD
• LTRIM
• PREPEND
• REPLACE
• REVERSE
• RIGHT
• RPAD
• RTRIM
• SUBSTR
• SUBSTRING
• TRIM
• UCASE
• UPPER

JQL is able to return the current date and time for the server, as well as month and day names for dates. As such, it has been developed with some built-in localization options that help when working with multi-language applications. If you require the month and day names in another language, you must first call the setLocale() method:

$jql->setLocale('nl_NL');

The above snippet will set the current locale to nl_NL.

To handle different timezones for time functions, simply call setTimezone() and specify the required timezone:

$jql->setTimezone('Europe/Paris');

This section contains some examples demonstrating the usage of JQL. If you discover during your own coding that any of the functions do not work as expected, please raise an issue via the Github repository.

All examples below assume the following JSON structure:

[
  {
    'id': 1,
    'forename': 'John',
    'surname':  'Doe',
    'age': 25,
    'birthday': '1995-05-24',
    'tags': [ 'red', 'green' ]
  },
  {
    'id': 2,
    'forename': 'Joe',
    'surname': 'Bloggs',
    'age': 50,
    'birthday': '1955-02-24',
    'tags': [ 'red', 'blue' ]
  },
  {
    'id': 3,
    'forename': 'Foo',
    'surname': 'Bar',
    'age': 12,
    'birthday': '2007-12-09',
    'tags': [ ]
  },
  {
    'id': 4,
    'forename': 'John',
    'surname': 'Boy',
    'age': 19,
    'birthday': '2018-09-09'
  }
]

Select the first and last name of each user, ordered by their surname and forename:

$jql = new JQL($json);

$jql->select([ 'forename', 'surname' ])
    ->order('surname', 'ASC')
    ->order('forename', 'ASC')
    ->fetch();

Return the full name for each user by concatenating their forename and surname fields:

$jql = new JQL($json);

$jql->select([ 'CONCAT_WS(forename, surname, \' \') AS full_name' ])
    ->fetch();

Get the average age of all users:

$jql = new JQL($json);

$jql->select([ 'AVG(age) AS average_age' ])
    ->fetchOne();

Retrieve the average age and concatenated full name for all users that are tagged with red:

$jql = new JQL($json);

$jql->select([ 'AVG(age) AS average_age', 'CONCAT_WS(forename, surname, \' \') AS full_name' ])
    ->where('tags CONTAINS red')
    ->fetch()

Update user 1 to have a new field containing full name, and save the resulting update to a JSON file named updated.json:

$jql = new JQL($json);

$jql->update([ 'full_name' => 'CONCAT_WS(forename, surname, \' \') AS full_name' ])
    ->where('id = 1')
    ->saveAsFile('updated.json');

Select the day on which the users were born, who are called John and are older than 18:

$jql = new JQL($json);

$jql->select([ 'DAYNAME(birthday) AS birth_day' ])
    ->where( 'forename = John AND age > 18' )
    ->fetch();

JQL is self-contained and does not have any external library or framework dependencies. However, to work as expected, the minimum PHP requirements are shown below:

• PHP version >= 5.6
• PHP json module enabled

JQL is still in development, and there's a hefty roadmap ahead of things I'd like to add to the library. If you'd like to contribute to the project, please get in touch!

• Support remote JSON files
• Support multiple where clause types (both AND and OR)

MIT License

Copyright (c) 2019 Ben Major

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.