> you get the added benefit of writing queries in JSON instead of raw SQL.
I’m sorry, I can’t. The tail is wagging the dog.
dang, can you delete my account and scrub my history? I’m serious.
I’m sorry, I can’t. The tail is wagging the dog.
dang, can you delete my account and scrub my history? I’m serious.
SELECT email FROM users WHERE deleted_at IS NOT NULL OR status = 'active'
{
"_select": "email",
"_table": "users",
"_where": {
"deleted_at": { "_is": { "_not": SQL_NULL_VALUE } },
"_or": [
{ "status": "inactive" },
]
}
}
JSON:
{"foo": ["bar", 42]}
<Object>
<Property>
<Key>foo</Key>
<Value>
<Array>
<String>bar</String>
<Number>42</Number>
</Arra>
</Value>
</Property>
</Object>
Edit: jesus christ, it actually exists https://sevenval.gitbook.io/flat/reference/templating/oxn
{
"dimensions": [
"users.state",
"users.city",
"orders.status"
],
"measures": [
"orders.count"
],
"filters": [
{
"member": "users.state",
"operator": "notEquals",
"values": ["us-wa"]
}
],
"timeDimensions": [
{
"dimension": "orders.created_at",
"dateRange": ["2020-01-01", "2021-01-01"]
}
],
"limit": 10
}
SELECT
users.state,
users.city,
orders.status,
sum(orders.count)
FROM orders
CROSS JOIN users
WHERE
users.state != 'us-wa'
AND orders.created_at BETWEEN '2020-01-01' AND '2021-01-01'
GROUP BY 1, 2, 3
LIMIT 10;> Please convert the following fragment of a programming language (auto-detect) into a json-like parsing information when language construct is represented like an object, fixed branches are represented like properties and iterative clauses (statement list for example) as array.
From a schema standpoint, table `orders` presumably has a row per order, with columns like `user_id`, `status` (as you stated), `created_at` (same), etc. Why would there be a `count` column? What does that represent?
From a query standpoint, I'm not sure what this would accomplish. You want the cartesian product of `users` and `orders`, filtered to all states except Washington, and where the order was created in 2020? The only reason I can think of to use a CROSS JOIN would be if there is no logical link between the tables, but that doesn't make any sense for this, because users:orders should be a 1:M relationship. Orders don't place themselves.
I think what you might have meant would be:
SELECT
users.state,
users.city,
orders.status,
COUNT(*)
FROM users
JOIN orders ON user.id = orders.user_id
WHERE
users.state != 'us-wa' AND
orders.created_at BETWEEN '2020-01-01' AND '2021-01-01'
GROUP BY 1, 2, 3
LIMIT 10;
Also, if you or anyone else is creating a schema like this, _please_ don't make this denormalized mess. `orders.status` is going to be extremely low cardinality, as is `users.state` (to a lesser extent), and `users.city` (to an even lesser extent, but still). Make separate lookup tables for `city` and/or `state` (you don't even need to worry about pre-populating these, you can use GeoNames[0]). For `status`, you could do the same, or turn them into native ENUM [1] if you'd like to save a lookup.
[1]: https://www.postgresql.org/docs/current/datatype-enum.html