[Best Practice] JSON as SQL vs JS Query vs JS Transformer?

Discussion
1

Like the title suggests, I'm wondering in which case you would use JSON as SQL instead of a JS Query or a JS Transformer. What are the best practices for maintainability as well as performance?

The main use-case that led me to this question is trying to join the data from multiple queries to our NoSQL db that have matching document ids. As an example, we have a buying report that needs to combine data from 4 separate collections using the item_id (orders, purchase_orders, items, inventory). Initially we used MongoDB's native aggregations which took 30-40s to run. Then we switched to a JS Query to do the joining (relying heavily on _.find() and _.filter() ) that cut down processing time to about 5s. In this case, would I gain any benefit from switching to JSON as SQL?

What about other cases? What kind of situations would benefit from using JSON as SQL? JS Queries? JS Transformers?

2

The scenario you suggest definitely calls for JSON SQL. If you can reason with the problem as linking multiple "tables" with a common ID then JSON SQL is the exact tool you need. If you have a basic understanding of SQL then it is far easier to build and understand than a bunch of JS array functions which I am sure you know can get pretty hairy real quick.

I can't guarantee any performance metrics, but JSON SQL queries have always been pretty darn quick for me.

I also use JSON SQL queries for filtering when I want to do it client side. Table's client side filtering outside of table - #3 by bradlymathews

You can also use JSON SQL to mock events on almost any property of any component. Watched Inputs for JS queries

Rules of thumb:

  1. If it is too easy to bother with a JSON SQL query then use a transformer.
  2. Is it easy to do in SQL? Use a JSON SQL query. Note you can pass additionalScope to a JSON query or use i from ListViews so you can pass in parameters.
  3. If it is way too hard to figure out the SQL to do it, or SQL just can't do it then use a transformer.
  4. If 2. or 3. and you need to pass parameters using additionalScope or i, use a JS Query and return a result (or set a temp var.)
2 Likes