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 and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page