-
-
Notifications
You must be signed in to change notification settings - Fork 2
JSON Sugars
Oxford Harrison edited this page Nov 19, 2024
·
16 revisions
💥 Model structure right within the language!
While you could stitch relevant SQL functions together to structure your output data, Linked QL supports special JSON-like syntaxes that abstract the rough work.
Syntax | Syntax | Equivalent Function (PostgreSQL) | Equivalent Function (MySQL) |
---|---|---|---|
Modelling objects | { expr1, expr2 AS key, ... } |
JSON_BUILD_OBJECT() |
JSON_OBJECT() |
Modelling arrays | [ expr1, expr2 ] |
JSON_BUILD_ARRAY() |
JSON_ARRAY() |
Aggregating expressions | expr1[] |
JSON_AGG() |
JSON_ARRAYAGG() |
// Model objects and arrays
const result = await client.query(
`SELECT
name,
email,
{ email, phone AS mobile } AS format1,
[ email, phone ] AS format2
FROM users`
);
console.log(result);
Console
[ { name: 'John Doe', email: '[email protected]', format1: { email: '[email protected]', mobile: '(555) 123-4567' }, format2: [ '[email protected]', '(555) 123-4567' ] }, { name: 'Alice Blue', email: '[email protected]', format1: { email: '[email protected]', mobile: '(888) 123-4567' }, format2: [ '[email protected]', '(888) 123-4567' ] } ]
// Aggregate a column
const result = await client.query(
`SELECT
email[] as emails
FROM users`
);
console.log(result);
Console
[ { emails: [ '[email protected]', '[email protected]' ] } ]