Skip to main content

An SQL query building specification to be used in conjunction with REST APIs and its implementation for peewee.

Project description

For the following Table example:

CREATE TABLE article (
    id integer NOT NULL,
    created timestamp without time zone NOT NULL,
    title character varying(255) NOT NULL,
    type_id integer NOT NULL,
    topic_id integer NOT NULL,
    author_ids integer[] NOT NULL,
    category_ids integer[],
    tags character varying(255)[],
    keywords character varying(255)[],
    summary text,
    content text NOT NULL,
    cover jsonb NOT NULL,
    editors_pick boolean NOT NULL,
    pageviews bigint NOT NULL,
    updated timestamp without time zone NOT NULL,
    published timestamp without time zone,
    permalink character varying(255),
    cust_meta jsonb
);

Specifications

  • For all articles with type_id equal to 1 (type_id = 1): json    {   "EQ": {      "type_id": 1   }    } Same structure is for:
Condition JSON KEY Symbol JSON Query
Less than LT < {"LT": {"type_id": 2}}
Less than or Equal to LE <= {"LE": {"type_id": 2}}
Greater than GT > {"GT": {"type_id": 2}}
Greater than or Equal to GE >= {"GE": {"type_id": 2}}
Not equal NE != {"NE": {"type_id": 2}}

IN

For all articles where type_id is in [1, 2, 3], the JSON query will be:

{
   "IN": {
      "pageviews": [1, 2, 3]
   }
}

BETWEEN

For all articles with pageviews between 10000 and 15000, the JSON query will be:

{
   "BETWEEN": {
      "pageviews": [10000, 15000]
   }
}

CONTAINS_ANY

For all articles where author_ids contains any of 8, 9, 10, the JSON query will be:

{
   "CONTAINS_ANY": {
      "author_ids": [8, 9, 10]
   }
}

CONTAINS_ALL

For all articles where author_ids contains all of 8, 9, the JSON query will be:

{
   "CONTAINS_ALL": {
      "author_ids": [8, 9]
   }
}

STARTSWITH

  • For all articles where title starts with Film Review, the JSON query will be:

    {
       "STARTSWITH": {
      "title": "Film Review"
       }
    }
    

Complex Queries

  • Complex queryies can contain nested structures of OR or AND or both.

For all articles with pageviews between 10000 and 15000 and whose author_ids contains 8(the author’s ID) (in above schema, author_ids is an ArrayField in Postgres), the JSON query will be:

{
   "AND": [
      {
         "BETWEEN": {
            "pageviews": [10000, 15000]
         }
      },
      {
         "CONTAINS": {
            "author_ids": [8]
         }
      }
   ]
}

Requirements

  • If there is only one condition, like pageviews > 100, the query can directly contain outermost key as one of EQ, NE, GT, GE, LT, LE, STARTSWITH, CONTAINS, CONTAINS_ALL, CONTAINS_ANY, BETWEEN.

example:

{
   "STARTSWITH": {
      "title": "Film Review"
   }
}
  • But if there are more conditions involved, the outermost key must be one of `OR

Project details


Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring DigiCert DigiCert EV certificate Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page