AlaSQL to search your JSON

english mobile

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:


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 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!

Karma Like a Boss

english mobile

For a long time, I thought you couldn't set a break point in a Jasmine test running in Karma. Heck, I even said that in a blog post. I recently learned this wasn't true. Once you know how debugging in Karma works, it seems really obvious, but I think I'm probably not alone in missing this entire feature or understanding how to make best use of it.

So let's start out by looking at a browser running Karma. When I first launched Karma with my first expect(true).toBe(true) test, I saw pretty much what everyone sees:

Of course, I clicked that honking obvious Debug button, and I got this:

Oh, thank you so much. What am I supposed to do with that? So I assumed like every end user since ever that the developer had screwed up and of course I wasn't missing something.

Meanwhile, I had the problem that all those console.log statements I kept dumping into Karma got really hard to read if it was set up to constantly rerun every time I changed a file because when I'd scroll up it was very difficult to find the beginning of any particular run. So I set Karma up to just run once and then exit so I could enter "cls" (clear screen) between runs. So the browser window closed after each run and for sure there were no break points being set.

Fast forward to the past couple of months and I've been working in Angular 2. Angular 2 and TypeScript are, in my opinion, a hot mess in terms of being able to figure out what's going on, so I've been researching hot and heavy into getting more insight when creating and running tests. So far none of that research is actually helping me with Angular 2, but when I recently worked on a coding challenge for a major software company--I am available for hire by the way--in AngularJS, all this research was pretty useful.

So let's unpick a few things that are really helpful to know:

  1. There is a reporter, karma clear screen reporter, that will clear the console at the beginning of each test run, which means you can let karma run continuously. Once you are at that point, you now have your Debug button back.
  2. When you open the Debug window, you're expected to use the browser's debug tools by pressing F12 or opening the developer tools however your favorite browser wants you to do that. At that point, you can set your break points and refresh the debug window to get access to all that debugging goodness.
  3. If you let Webstorm set up your Angular project for you, it does kind of a junit thing that by default runs karma only once every time you click the "run" button (don't use the debug button or you can't see the tree of your unit tests), but it leaves the karma browser window open. This leaves a nice clean terminal display, still gives you access to the Debug button, and shows you colorful little icons that indicate the status of your tests.

My mother like to say "the obvious, isn't." This was a lesson for me in how true this is. I hope this helps someone not go quite so long making the mistakes I did.