Skip to content

benmajor/JQL

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

17 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

JQL

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.

Table of Contents:

  1. Installation
  2. Usage
  3. Function Reference
  4. Localization
  5. Examples
  6. Requirements
  7. Roadmap
  8. License

1. Installation:

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.

2. Usage:

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.

Selecting specific columns:

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'
]);

Updating specific columns:

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.

Searching data using where():

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:

Operator Description Example
= Equal to field = 10
== Identical to (respects case) field == 'John Doe'
> Greater than field > 10
>= Greater than or equal to field >= 10
< Less than field < 10
<= Less than or equal to field <= 10
!= Not equal to field != 10
<> Greater than or less than field <> 10
LIKE Partially matches a string using the % wildcar - identical to SQL's LIKE clause surname LIKE '%wor%'
SLIKE Similar to LIKE, but strictly matches case. surname SLIKE 'Maj%'
NOT LIKE Opposite of LIKE; selects all records that DON'T match the specified pattern. surname NOT LIKE '%major'
NOT SLIKE Similar to NOT LIKE, but strictly matches case. surname NOT SLIKE '%or'
IN Where specified string exists in a set. forename IN (Doe, Major)
NOT IN Oppose of IN. forename NOT IN (Doe, Major)
CONTAINS Called on an array field, checks if the array contains the specified value. tags CONTAINS red
IS NULL Value is null. age IS NULL
IS NOT NULL Value is not null. age IS NOT NULL
IS EMPTY Array value is empty. tags IS EMPTY
IS NOT EMPTY Opposite of IS EMPTY. tags IS NOT EMPTY

Sorting results:

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');

Limiting Results:

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);

Executing the query:

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.

3. Function Reference:

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.

select() functions:

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

String functions:

Function name Description
APPEND(field, append) Appends append to the end of the value in field.
CHAR_LENGTH(field) Returns the chatacter length of field as an integer.
CHARACTER_LENGTH(field) As above.
CONCAT(field1, field2) Concatenate two fields into a single string.
CONCAT_WS(field1, field2, str) Concatenate two fields into a single string using the specified separator
DISTINCT(field) Returns a list of unique values for field. If field is an array, the function will merge all arays and return a distinct list. Otherwise, it merges all string values and returns a distinct list.
FORMAT(field, sf) Format the specified field into a float value using the specified number of significant figures; adds decimal points and thousand separators.
LCASE(field) Convert a field to lower case.
LEFT(field, chars) Returns chars characters from the left of the specified field.
LOWER(field) Alias of LCASE
LPAD(field, pad, length) Pad the specified field with the string specified in pad to length on the left of the field.
LTRIM(field) Trim any whitespace on the left-hand side of field.
PREPEND(field, prepend) Prepends prepend to the beginning of the value in field.
REPLACE(field, find, replace) Replace all instances of find with replace in field.
REVERSE(field) Reverse the string specified in field.
RIGHT(field, chars) Returns chars characters from the right of the specified field.
RPAD(field, pad, length) Pad the specified field with the string specified in pad to length on the right of the field.
RTRIM(field) Trim any whitespace on the right-hand side of field.
SUBSTR(field, chars, offset) Returns a substring of field, with chars characters, starting at offset.
SUBSTRING(field, chars, offset) Alias of SUBSTR(), see above.
TRIM(field) Trim all whitespace from both sides of field.
UCASE(field) Convert the specified field value to uppercase.
UPPER(field) Alias of UCASE(), see above.

Numeric functions:

Function name Description
ABS(field) Returns the absolute value of field.
ACOS(field) Returns the arc cosine of field.
ASIN(field) Returns the arc sine of field.
ATAN(field) Returns the arc tangent of field.
CEIL(field) Round up field to the nearest integer.
CEILING(field) Alias of CEIL(), see above.
COS(field) Returns the cosine of field.
COT(field) Returns the cotangent of field.
FLOOR(field) Round down field to the nearest integer.
RAND(arg1, arg2) Returns a random number using a Mersenne Twister RNG. If only arg1 is specified, it will be used as a seed for the PRNG. If both arg1 and arg2 are specified, they will be used as the minimum and maximum random numbers to generate.
RANDOM(arg1, arg2) Alias of RAND(), see above.
SIN(field) Returns the sine of field.
SQRT(field) Returns the square root of field.
TAN(field) Returns the tangent of field.

Date functions:

Function name Description
CURDATE() Returns the current date in YYYY-MM-DD format.
CURRENT_DATE() Alias of CURDATE(), see above.
CURTIME() Returns the current time in HH:mm:ss format.
CURRENT_TIME() Alias of CURRENT_TIME(), see above.
CURRENT_TIMESTAMP() Returns the number of seconds that have currently elapsed since the Unix epoch.
DATE_FORMAT(field, format) Format the date held in field using format. Please note that format should match the tokens used for PHP's strftime() function.
DAY(field) Returns the day of the month for the given date in field.
DAYNAME(field) Returns the name of the day for the given date in field.
DAYOFMONTH(field) Alias of DAY(), see above.
DAYOFWEEK(field) Returns the weekday index for the given date in field.
DAYOFYEAR(field) Returns the day of the year for the given date in field.
HOUR(field) Returns the number of hours for the given date in field.
LAST_DAY(field) Returns the last day for the month of the date in field. Can be used to retrieve the number of days for the given date.
MINUTE(field) Returns the number of minutes for the given date in field.
MONTH(field) Returns the month number for the given date in field.
MONTHNAME(field) Returns the name of the month for the given date in field.
NOW() Returns the current date and time in YYYY-MM-DD HH:mm:ss format.
SECOND(field) Returns the number of seconds for the given date in field.
TIMESTAMP(field) Converts the date in field to a Unix timestamp.
WEEK(field) Returns the week number for the given date in field.
YEAR(field) Returns the year for the given date in field.

Aggregate functions:

Function name Description
AVG(field) Returns the average value (mean) of field from all matched records.
MAX(field) Returns the maximum numeric value of field from all matched records.
MIN(field) Returns the minimum numeric value of field from all matched records.
SUM(field) Returns the sum value of field from all matched records.

update() functions:

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

6. Localization:

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');

5. Examples:

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'
  }
]

Minimal Working Example:

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();

Using functions:

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();

Using aggregate functions:

Get the average age of all users:

$jql = new JQL($json);

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

Using where():

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()

Updating a record:

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');

Date functions:

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();

6. Requirements:

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

7. Roadmap:

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)

8. License:

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.

About

No description, website, or topics provided.

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages