AlaSQL to search your JSON

When you're working in JavaScript, many times you'll have a data source that is one or more JSON or JSON-like objects. When those objects are stored in an Array, you can use filter() to find the element you need, but what happens when you start to get into nested Objects—where the JSON elements in your Array themselves contain Arrays or Objects? Then you wind up with filters within filters, null checking to avoid checking properties on objects that may or may not be there, etc. Wouldn't it be nice if you could just query the objects?

With AlaSQL, you can! Note that AlaSQL can do a lot more than just querying JSON, but I'm focusing on that because that's how I'm using it at the moment. The documentation on the syntax of how to do this is written from the perspective of someone who already understands the syntax, so it doesn't really cover all the use cases I ran into. Between trawling the tests and a great deal of trial and error, I've arrived at a "layman's" understanding of how some of this works, so I thought I'd share my thoughts to make it easier for more people to use this great tool.

Here's a plunker that you can launch in a separate window to follow along:

SEARCH "/"

The forward slash just represents a "level" in the JSON Object/Array. It's especially useful when dealing with a level that's an Array, because that level won't have named properties (see example 1).

The forward slash both says where to search and what to return. So it navigates to a specific depth for both purposes (see example 2). In this example, the first "/" is the root of the Array. The second is the properties of the Objects returned, and the third says "look inside those properties." So it's the same as example 3, because categories is the only property with anything nested in it. In example 3, you can think of "categories" as being a more specific version of "/".

Returning values at a different level than where you're searching

There are two instances in which you need to return elements at a different level than you're searching: one where you need to find some ancestor element where child objects satisfy the query and one where you need to go further and get properties from the objects satisfying the query.

I'm going to start with the second first, because it's simpler. All you have to do is add the property you're looking for between the end of your entire SEARCH statement and the beginning of the FROM (esample 4). Usually that means it's going to go after WHERE (and ORDER BY, which we'll get to later), which is a little odd if you're used to database SQL, but it makes sense if you think of the result of the SEARCH as an object and then you're saying "and then I want this from the returned object."

In the case of finding an ancestor, you can kind of think of it as using a variable to say "hold that thought" at a specific level of navigation as you're drilling down, by storing that level in a variable. Then, just as you do with a property, you ask AlaSQL to return that variable from the search result (example 5).

DISTINCT, ORDER BY

DISTINCT with SEARCH is similar to DISTINCT with SELECT in regular SQL in that it wants to be right after SEARCH. It uses parentheses to enclose what should be distinct, and those should go around the entire statement that says what the SEARCH criteria are (example 6).

Like variables referring to ancestors, ORDER BY goes before FROM. It also has a special syntax for ordering by "whatever it was that the search selected." This is handy, because it can get kind of fiddly trying to figure out what the path to that should be in the ORDER BY clause. To use this syntax, just put ASC or DESC in parentheses after ORDER BY (example 7). Note that you can also just leave the parentheses empty, which is the same as ASC.

Other use cases

I'm sure I've only scratched the surface ot the SEARCH syntax, but these were a few that I ran into in my own project. I've created a plunker so that I can explore different use cases without having to wait for my Angular 2 TypeScript project to compile every time I make a change. Feel free to use it as well!