Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Testing N1QL: SQL for JSON (Part 3)

DZone's Guide to

Testing N1QL: SQL for JSON (Part 3)

Following along with the series, get a look at how MySQL's Random Query Generator can help you make templates for your own queries with JSON in mind.

· Database Zone ·
Free Resource

Download the Altoros NoSQL Performance Benchmark 2018. Compare top NoSQL solutions – Couchbase Server v5.5, MongoDB v3.6, and DataStax Enterprise v6 (Cassandra).

Welcome back to Testing N1QL! If you're just getting in, check out part one and part two. Now that we've been introduced to the Random Query Generator, let's dive into an example of how you can use it with N1QL.

Example of Grammar used by N1QL:

query:
       select ;

select:
       SELECT select_from FROM BUCKET_NAME WHERE complex_condition;

complex_condition:
        (condition) AND (condition) | (condition) OR (condition) | condition;

condition:
         numeric_condition | string_condition ;

select_from:
  COUNT(*) |  COUNT( field ) | SUM( non_string_field ) | SUM(DISTINCT non_string_field ) |       AVG( non_string_field ) | AVG( DISTINCT non_string_field ) |  MAX( non_string_field ) | MIN( non_string_field );

numeric_condition:
  numeric_field < numeric_value |
  numeric_field = numeric_value |
  numeric_field > numeric_value |
  numeric_field  >= numeric_value |
  numeric_field  <= numeric_value |
  (numeric_condition) AND (numeric_condition)|
  (numeric_condition) OR (numeric_condition)|
  NOT (numeric_condition) |
  numeric_between_condition |
  numeric_is_not_null |
  numeric_not_equals_condition |
  numeric_is_null |
  numeric_in_condition ;

string_condition:
  string_field < string_values |
  string_field > string_values |
  string_field  >= string_values |
  string_field  <= string_values |
  (string_condition) AND (string_condition) |
  (string_condition) OR (string_condition) |
  string_not_between_condition |
  NOT (string_condition) |
  string_is_not_null |
  string_is_null |
  string_not_equals_condition |
  string_in_conidtion |
  string_like_condition |
  string_equals_condition |
  string_not_like_condition ;

string_equals_condition:
  string_field = string_values;

string_not_equals_condition:
  string_field != string_values | string_field <> string_values ;

string_between_condition:
  string_field BETWEEN LOWER_BOUND_VALUE and UPPER_BOUND_VALUE;

string_not_between_condition:
  string_field NOT BETWEEN LOWER_BOUND_VALUE and UPPER_BOUND_VALUE;

string_is_not_null:
  string_field IS NOT NULL;

string_in_conidtion:
  string_field IN ( string_field_list );

string_is_null:
  string_field IS NULL;

string_like_condition:
  string_field LIKE 'STRING_VALUES%' | string_field LIKE '%STRING_VALUES' | string_field LIKE STRING_VALUES | string_field LIKE '%STRING_VALUES%';

string_not_like_condition:
  string_field NOT LIKE 'STRING_VALUES%' | string_field NOT LIKE '%STRING_VALUES' | string_field NOT LIKE STRING_VALUES |  string_field NOT LIKE '%STRING_VALUES%';

field_list:
  NUMERIC_FIELD_LIST | STRING_FIELD_LIST | NUMERIC_FIELD_LIST, STRING_FIELD_LIST |     NUMERIC_FIELD_LIST, STRING_FIELD_LIST, BOOL_FIELD_LIST;

field:
  NUMERIC_FIELD | STRING_FIELD;


We use the RANDGEN Perl script to generate different patterns of templates using this grammar.

Example Perl command:

perl gensql.pl –grammar=<path to specific grammar file> --queries=<number of queries>  --seed=573 > template


Example of a simple query template generated by RQG:

select * 

from bucket_name

where numeric_field=numeric_value


Example of an aggregate query template generated by RQG:

SELECT SUM(DISTINCT NUMERIC_FIELD ) 

FROM BUCKET_NAME

WHERE ((BOOL_FIELD = true OR NUMERIC_FIELD < NUMERIC_VALUE)) 

AND ((STRING_FIELD NOT LIKE STRING_VALUES AND NUMERIC_FIELD IN ( LIST ))) 

ORDER BY NUMERIC_FIELD_LIST;


We identified a standard relational database, Flightstats, and created our own database schema in MySQL. We created different database definitions for different kinds of operations. Each database definition corresponds to a single schema in MySQL, and each MySQL table is mapped to a Couchbase bucket and defines sufficient columns to map to different data types being tested, since we need to test different JSON data types.

An example of mapping MySQL schema to Couchbase Bucket is shown in Figure 2 below:

Image title

Fig. 2: Data mapping

That's where we're going to wrap up for today. It's a bit short, but it's important to get a look at how Randgen works in action. Next time, we'll dive into using the templates we just made to generate queries. 

Download the whitepaper, Moving From Relational to NoSQL: How to Get Started. We’ll take you step by step through your first NoSQL project.

Topics:
json ,grammar ,tutorial ,database ,data generation

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}