I've invented an SQL-like language. Let's call it SelectQL. You can't INSERT anything, you can only SELECT, and I don't have the time for an interpreter. How about you help?

The Challenge

Write a full program or a function that takes in a database of tables and a valid SelectQL SELECT string, interprets the string, and outputs the resulting SELECTion.

SELECT Specification

The SELECT SelectQL statement is as follows (optional portions are in curly brackets):

SELECT [column] FROM [table] {WHERE ( [condition] {[AND/OR] [condition] ... } ) }
  • [column] can be ALL for selecting all columns, or a valid column name in the [table].
  • [table] is the name of a table.
  • The WHERE clause is optional. A WHERE clause is followed by a list of [condition] separated by any of AND or OR, surrounded by parentheses ().
  • A [condition] is a key-value pair separated by an equals sign =.
  • [condition] can be preceded with NOT to select the complement of the condition.
  • Operator Precedence: NOT has higher precedence than AND and OR. AND and OR is executed left to right.
  • Conditions involving arrays/lists ({980, "abc"} in data below) will look for the existence of a requested value in the array, and return the whole array if true.
  • key must be a column in [table].
  • SelectQL query strings are case insensitive.

Table Format

Your program should take in an SelectQL database (array of arrays, list of lists, delimited string, JSON object, etc) in the following format:

    [table name, (column names), [(row), (row), ...]],
    [table name, (column names), [(row), (row), ...]]
  • table name is the name of the table.
  • [column names] is a list of unique strings which are column names. These are the keys in [condition].
  • Each column's values all must have the same type.
  • [row] is a list-like of values. Each row must be the same length as [column names].
  • There can be no [row]s.
  • Elements in [row] cannot be empty. Empty strings or arrays are not considered empty.


There are three types in SelectQL: number, string, array.

Numbers are at least 1 digit in 0-9, with an optional decimal point with at least 1 digit.

Strings are double-quote-delimited sequences of characters. Empty strings are possible ("").

Arrays are curly-bracket-delimited comma-separated sequences of numbers, strings, or arrays. Arrays can be heterogeneous. Empty arrays are possible ({}).


  • A valid SelectQL database, and
  • A valid SelectQL SELECT query. It will always SELECT a valid table in the database.


  • The result of the query. The result must have the columns as well as the resulting rows.


The examples will use the following database:

    [data, (d1, d2, 3, ~!@#), [(123, "", {980, "abc"}, "false"), (456, "this isn't empty!", {{},{}}, "-123.4444"), (1, "2", {3, 4}, "five")]],
    [no, (yes, a, 1.5, what), []]
string --> result
        --> [(d1, D2, 3, ~!@#), [(123, "", {980, "aBc"}, "false"), (456, "this isn't empty!", {{},{}}, "-123.4444"), (1, "2", {3, 4}, "five")]]

        --> [(d1), [(123), (456), (1)]]

"SELECT d1, 3 FROM data"
        --> [(d1, 3), [(123, {980, "aBc"}), (456, {{},{}}), (1, {3,4})]]

        --> [(d1, D2, 3, ~!@#), [(456, "this isn't empty!", {{},{}}, "-123.4444"), (1, "2", [3, 4], "five")]]

"SELECT all FROM data WHERE (d1=123)"
        --> [(d1, D2, 3, ~!@#), [(123, "", [980, "aBc"], "false")]

"SELECT ~!@# FROM DATA WHERE (3={} OR d2="")"
        --> [(~!@#), [("false"), ("-123.4444")]

        --> [(d1, d2, 3, ~!@#), []]

"select all from no"
        --> [(yes, a, 1.5, what), []]

"select 3 from data where (3=980)"
        --> [(3),[[{980, "aBc"}]]

Additional Rules/Clarifications

Sandbox Link

  • 4
    \$\begingroup\$ May AND and OR be mixed together? If so, what is their precedence? \$\endgroup\$
    – Arnauld
    Commented May 18, 2020 at 15:06
  • 1
    \$\begingroup\$ I guess column1 = column2 comparisons are not supported by the language. \$\endgroup\$
    – manatwork
    Commented May 18, 2020 at 15:11
  • \$\begingroup\$ Left to right, so A=1 AND B=2 OR C=3 AND D=4 is ((A==1 and B==2) or C==3) and D==4 \$\endgroup\$
    – bigyihsuan
    Commented May 18, 2020 at 15:12
  • 1
    \$\begingroup\$ Can we assume there won't be any spaces between the column = value checks? \$\endgroup\$ Commented May 18, 2020 at 15:39
  • 1
    \$\begingroup\$ What does a row like [123, "", [980, "abc"], "false"], mean, in particular, the third value. How does that work with the = operator? \$\endgroup\$
    – Abigail
    Commented May 18, 2020 at 16:07

3 Answers 3


Python 3.8 (pre-release), 473 bytes

from re import*
def s(d,q):
 o,f,c=search(r' (?:all|(.*?)) from (\S+)(?:.*?\((.*)\))?',q,I).groups()
 for x,l,r in d:
  u=str.lower;h=bool;m=o and o.split(', ')or l;y=[]
  for g in r:
   p=0;_,*t=split('(?: (?:(and)|or) |^)(not )?(.*?)=',c or'',0,I)
   while t:
    p=h(a)&p&(b:=h(n)^any(i==u(k)and(j==eval(v)or list==type(j)and eval(v)in j)for i,j in zip(l,g)))|p|b
   if 1-h(c)|p:y+=[j for i,j in zip(l,g)for z in m if i==u(z)],
  if u(f)==x:print([m,y])

Try it online!

This uses @hyper-neutrino's style of input/output, but is otherwise a completely different implementation.

-5 bytes thanks to @cairdcoinheringaahing.

  • \$\begingroup\$ Assuming a global variable is not allowed. \$\endgroup\$
    – Bubbler
    Commented May 3, 2021 at 8:31
  • \$\begingroup\$ I see. Updated as instructed. Thanks. \$\endgroup\$
    – blhsing
    Commented May 3, 2021 at 8:37
  • \$\begingroup\$ -5 bytes \$\endgroup\$ Commented May 3, 2021 at 21:53
  • \$\begingroup\$ @cairdcoinheringaahing Ah, did not realize that tuple assignment is more wasteful than semicolon-separated simple statements. The redundant space before the star too. Thanks! \$\endgroup\$
    – blhsing
    Commented May 4, 2021 at 0:43

Python 3, 682 bytes

from regex import*
s=lambda x:str==type(x)and x.lower()or list==type(x)and[*map(s,x)]or x
E=lambda x:str(V(x.group()))
def f(d,q):
	c,t,w=match("select (.+) from (.+?)(?: where \((.+)\))?$",s(q)).groups()
	K=[[C for C in map(s,y)if{*c.split(", ")}&{"all",C}]for x,y,z in d if s(x)==t][0];r=[]
	for R in{s(x):[{s(k):l for k,l in zip(y,r)}for r in z]for x,y,z in d}[t]:
		if w:
			v=sub("not \w+",E,sub(r'(\S+)=(\[((?2)(, *(?2))*)?\]|"[^"]*"|\d+)',lambda x:str(e(s(R[x.group(1)]),s(V(x.group(2))))),w))
			while len(v)>5:v=sub("\w+ \w+ \w+",E,v,1)
	print([K,[[q[x]for x in K]for q in r]])
e=lambda x,y:x==y or list==type(x)and any(e(v,y)for v in x)

Try it online!

-5 bytes thanks to Razetime
-18 bytes thanks to caird coinheringaahing

this was painful

This uses [] for arrays because that's Python's list representation. Please let me know if this is invalid. The DB is just taken in the representation given in the question as a native Python object.

  • 1
    \$\begingroup\$ c is only used once so you can inline c.split \$\endgroup\$
    – Razetime
    Commented Apr 29, 2021 at 5:14
  • 1
    \$\begingroup\$ [] is perfectly fine for arrays \$\endgroup\$
    – bigyihsuan
    Commented Apr 29, 2021 at 6:07
  • 1
    \$\begingroup\$ -18 bytes \$\endgroup\$ Commented May 3, 2021 at 21:48

Python 3, 583 bytes:

import re, operator as o
def f(a,q):
 [[c,b]],z=[(c,d)for t,c,d in a if t==(r:=re.findall(' (.+) FROM (\w+)',q)[0])[1]],lambda n,m,w:[o.pos,o.not_][w](n in m if type(m)==set else n==m)
 d=[dict(zip(c,i))for i in b]
 p=lambda e,i:(eval((v:=e.pop().split('='))[-1]),i[re.sub('NOT\s','',v[0])],v[0][:3]=='NOT')
 return (g:=[c,r[0].split(', ')][r[0]!='ALL']),[[i[j]for j in g]for i in d if(y:=lambda e,u:True if not e else(z(*p(e,i))if not e[:-1]or not u else[o.and_,o.or_][u.pop()=='OR'](z(*p(e,i)),y(e,u))))(re.findall('(?:(?:NOT\s)*\w+\=(?:\{.*?\}|".*"|\w+))+', q),re.findall('AND|OR',q))]
  • \$\begingroup\$ welcome to codegolf! Nice first answer! But can you add a TIO link that run the code? this way others can help you golf down more bytes \$\endgroup\$
    – okie
    Commented Nov 29, 2021 at 23:38
  • \$\begingroup\$ here’s a TIO link for your code \$\endgroup\$
    – okie
    Commented Nov 29, 2021 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.