I usually use SQL, but when I am working with array data in JavaScript, I sometimes wish I could write queries in SQL for JavaScript arrays.

On the other hand, C# has a language called LINQ that can process arrays with SQL-like queries.

There was a library that ported LINQ to JavaScript as well, so I used it this time.

After using LINQ, I found that I could do everything I initially wanted to do in SQL with LINQ, so I would like to use it from now on.

I like to use utility libraries quickly when they come to mind, but JavaScript LINQ was a bit complicated to write without looking at anything.

So, I will summarize how to write the operations we often do in SQL in JavaScript LINQ.

What is LINQ?

LINQ is an embedded language in C# that allows arrays to be processed with SQL-like queries in C#.

Here, we use the linq library, which allows LINQ to be used in JavaScript. However, it is not Microsoft official.

install

There are two versions: ES module version and CommonJS version. CommonJS must be installed by specifying the version using the following method.

npm install linq@3

Basic usage

import * as Enumerable from 'linq';

(async ()=>{
    try{
        const user = [
            { user_id: 1, name: "AAA" },
            { user_id: 2, name: "BBB" },
            { user_id: 3, name: "CCC" },
        ];

        const res = Enumerable
            .from(user)
            .where(row => return row.user_id <= 2)
            .orderByDescending(row => row.user_id)
            .select((row) => {
                return {
                    ...row,
                    id_name: `${row.name}(${row.user_id})`,
                };
            })
            .toArray();

        /*
        [
          { user_id: 2, name: 'BBB', id_name: 'BBB(2)' },
          { user_id: 1, name: 'AAA', id_name: 'AAA(1)' }
        ]
        */
        console.log(res);

    }catch(err){
        console.error(err);
    }
})();

explanation

There are two ways to write LINQ in C#: query expression, which is written as SQL-like statements, and method syntax, which is written as a function, but only method syntax is available in JavaScript's linq
Data is handled as Enumerable objects
Enumerable is like a database table
Enumerable has functions such as where() and orderBy() defined, and Enumerable is converted.
The function Enumerable returns Enumerable after conversion. This allows successive conversions in the method chain.
from() generates Enumerable from a JavaScript array
Since Enumerable is not a JavaScript array, it is converted back to an array with toArray() at the end.
The order and number of functions are not important, since it is just a method chain of Enumerable.
- There is no problem if orderBy() is followed by where() or where() is called multiple times.
- If Enumerable is output, select() is also unnecessary

List of usage

sample data

const purchase = [
    { event_date: "2023-01-01", user_id: 1, sales: 100 },
    { event_date: "2023-01-01", user_id: 1, sales: 200 },
    { event_date: "2023-01-01", user_id: 2, sales: 1000 },
    { event_date: "2023-01-02", user_id: 999, sales: 10000 },
];

const user = [
    { user_id: 1, name: "AAA" },
    { user_id: 2, name: "BBB" },
];

const filter = [
    { event_date: "2023-01-01", user_id: 1 },
    { event_date: "2023-01-01", user_id: 2 },
];

select()

Process data

const res = Enumerable
    .from(purchase)
    .select(row => {
        return {
            event_date: row.event_date,
            user_id: row.user_id,
            salesX100: row.sales * 100,
        }
    })
    .toArray();

/*
[
    { event_date: '2023-01-01', user_id: 1, salesX100: 10000 },
    { event_date: '2023-01-01', user_id: 1, salesX100: 20000 },
    { event_date: '2023-01-01', user_id: 2, salesX100: 100000 },
    { event_date: '2023-01-02', user_id: 999, salesX100: 1000000 }
    ]
*/
console.log(res);

Output as is

If you don't process data, you don't need select().

const res = Enumerable
    .from(purchase)
    .toArray();

/*
[
    { event_date: '2023-01-01', user_id: 1, sales: 100 },
    { event_date: '2023-01-01', user_id: 1, sales: 200 },
    { event_date: '2023-01-01', user_id: 2, sales: 1000 },
    { event_date: '2023-01-02', user_id: 999, sales: 10000 }
]
*/
console.log(res);

where()

Specify the conditions for retrieval

const res = Enumerable
    .from(purchase)
    .where(row => {
        return (
            row.sales >= 1000 &&
            row.event_date === "2023-01-01"
        );
    })
    .toArray();

/*
[ { event_date: '2023-01-01', user_id: 2, sales: 1000 } ]
*/
console.log(res);

Multiple calls

You can specify a condition multiple times to narrow down your search without specifying it once.

const res = Enumerable
    .from(purchase)
    .where(row => row.sales >= 1000)
    .where(row => row.event_date === "2023-01-01")
    .toArray();

/*
[ { event_date: '2023-01-01', user_id: 2, sales: 1000 } ]
*/
console.log(res);

orderBy()

If you specify an order key, it will sort by that key.
Use method chaining when sorting by multiple keys
- However, since the next sort is performed after the sort is determined, the order of description is opposite to that of SQL.
Descending order is orderByDescending().

const res = Enumerable
    .from(purchase)
    .orderByDescending(row => row.sales)
    .orderBy(row => row.event_date)
    .toArray();

/*
[
  { event_date: '2023-01-01', user_id: 2, sales: 1000 },
  { event_date: '2023-01-01', user_id: 1, sales: 200 },
  { event_date: '2023-01-01', user_id: 1, sales: 100 },
  { event_date: '2023-01-02', user_id: 999, sales: 10000 }
]
*/
console.log(res);

groupBy()

Specify a group key and group by that key
argument
- the first
  - Group key
- second (in a series)
  - Element of rows in the third function argument." No problem to leave it as null.
- third (in a series)
  - result
    - key is the group key created by the first argument." rows is the list of elements included in the grouping with that group key.
    - The result is one row, so rows needs to be tabulated
The result of the operation can be used as a group key.

const res = Enumerable
    .from(purchase)
    .groupBy((row) => row.event_date,
        null,
        (key, rows) => {
            return {
                event_date: key,
                count: rows.count(),
            }
        }
    )
    .toArray();
/*
[
  { event_date: '2023-01-01', count: 3 },
  { event_date: '2023-01-02', count: 1 }
]
*/
console.log(res);

composite key

A key can have multiple elements to make it a composite key.
Since the keys are identified by the === operator, they are compared by serialized values with JSON.stringify() so that they can be compared by value.

const res = Enumerable
    .from(purchase)
    .groupBy((row) => {
        return JSON.stringify({
            event_date: row.event_date,
            sales_threshold: row.sales >= 500,
        });
    },
        null,
        (key, rows) => {
            return {
                ...JSON.parse(key),
                count: rows.count(),
            }
        },
        (key) => JSON.stringify(key),
    )
    .toArray();

/*
[
  { event_date: '2023-01-01', sales_threshold: false, count: 2 },
  { event_date: '2023-01-01', sales_threshold: true, count: 1 },
  { event_date: '2023-01-02', sales_threshold: true, count: 1 }
]
*/
console.log(res);

INNER JOIN

Using join()
argument
- the first
  - right table
- second (in a series)
  - Binding key for left
- third (in a series)
  - Binding key for right
- fourth (in a series)
  - result

const res = Enumerable
    .from(purchase)
    .join(user,
        left => left.user_id,
        right => right.user_id,
        (left, right) => {
            return {
                ...left,
                user_name: right.name,
            }
        }
    )
    .toArray();

/*
[
  { event_date: '2023-01-01', user_id: 1, sales: 100, user_name: 'AAA' },
  { event_date: '2023-01-01', user_id: 1, sales: 200, user_name: 'AAA' },
  { event_date: '2023-01-01', user_id: 2, sales: 1000, user_name: 'BBB' }
]
*/
console.log(res);

composite key

Same as groupB()
A key can have multiple elements to make it a composite key.
Since the keys are identified by the === operator, they are compared by serialized values with JSON.stringify() so that they can be compared by value.

LEFT JOIN

LINQ does not have Left Join functionality.
Implementation using groupJoin() and selectMany()

groupJoin()

Join the rows of the Right table corresponding to the Left table with an array
Although it does not expand arrays like Left Join, groupJoin() is easy to use, so it is worth remembering.
Arrays are passed as Enumerable, so to actually use them, convert them to JavaScript arrays using toArray().
argument
- the first
  - table right
- second (in a series)
  - Join key for table left
- third (in a series)
  - Join key for table right
- fourth (in a series)
  - result
    - Row of table left as the first argument
    - Array of rows from table right where the second argument is a join to the rows of table left
      - (In some cases, multiple rows of table right are joined to one row of table left)

const res = Enumerable
    .from(purchase)
    .groupJoin(user,
        left => left.user_id,
        right => right.user_id,
        (left, rightRows) => {
            return {
                ...left,
                right: rightRows.toArray(),
            };
        }
    )
    .toArray();

/*
[
  {
    event_date: '2023-01-01', user_id: 1, sales: 100,
    right: [ { user_id: 1, name: 'AAA' } ]
  },
  {
    event_date: '2023-01-01', user_id: 1, sales: 200,
    right: [ { user_id: 1, name: 'AAA' } ]
  },
  {
    event_date: '2023-01-01', user_id: 2, sales: 1000,
    right: [ { user_id: 2, name: 'BBB' } ]
  },
  {
    event_date: '2023-01-02', user_id: 999, sales: 10000,
    right: []
  }
]
*/
console.dir(res, { depth: null });

composite key

Same as groupBy() and join()
A key can have multiple elements to make it a composite key.
Since the keys are identified by the === operator, they are compared by serialized value with JSON.stringify() so that they can be compared by value.

selectMany()

Expands an array when the elements of a row contain arrays.
argument
- the first
  - Specifies which elements are arrays
  - If the array is empty, no expansion is performed. To enable expansion even for empty arrays, add defaultIfEmpty().
- second (in a series)
  - result
  - The argument is a row from the left table and an array of rows from the right table, one of which is expanded and passed.

Implement LEFT JOIN using groupJoin() and selectMany()

Points to note
- Do not change rightRows of groupJoin() to toArray(), but pass it as Enumerable.
- defaultIfEmpty() is required
  - Without defaultIfEmpty(), if there is no row in table right matching the row in table left, the record in table left itself is not created. In other words, the behavior is the same as for Inner Join.
When an argument is passed to defaultIfEmpty(), the argument value is used as the value of a blank line in right
- For example, defaultIfEmpty({ user_id: null, name: null }) in the following example

const res = Enumerable
    .from(purchase)
    .groupJoin(user,
        left => left.user_id,
        right => right.user_id,
        (left, rightRows) => {
            return {
                ...left,
                rightRows,
            };
        }
    )
    .selectMany((row) => row.rightRows.defaultIfEmpty(),
        (left, right) => {
            delete left.rightRows;
            return {
                ...left,
                right,
            }
        }
    )
    .toArray();

/*
[
  {
    event_date: '2023-01-01', user_id: 1, sales: 100,
    right: { user_id: 1, name: 'AAA' }
  },
  {
    event_date: '2023-01-01', user_id: 1, sales: 200,
    right: { user_id: 1, name: 'AAA' }
  },
  {
    event_date: '2023-01-01', user_id: 2, sales: 1000,
    right: { user_id: 2, name: 'BBB' }
  },
  { event_date: '2023-01-02', user_id: 999, sales: 10000,
    right: null
  }
]*/
console.log(res);

CROSS JOIN

Pass right table as the first argument of selectMany()
- Every time a row of table left is called, all rows of table right are returned

const res = Enumerable
    .from(purchase)
    .selectMany(row => user,
        (left, right) => {
            return {
                left,
                right,
            };
        }
    )
    .toArray();

/*
[
  {
    left: { event_date: '2023-01-01', user_id: 1, sales: 100 },
    right: { user_id: 1, name: 'AAA' }
  },
  {
    left: { event_date: '2023-01-01', user_id: 1, sales: 100 },
    right: { user_id: 2, name: 'BBB' }
  },
  ...
  ...
  ...
]*/
console.log(res);

UNION ALL

Match the two arrays with concat()

const userAdd = Enumerable
    .from(user);

const res = Enumerable
    .from(user)
    .concat(userAdd)
    .toArray();

/*
[
  { user_id: 1, name: 'AAA' },
  { user_id: 2, name: 'BBB' },
  { user_id: 1, name: 'AAA' },
  { user_id: 2, name: 'BBB' }
]
*/
console.log(res);

LIMIT & TOP

Extract the first specified number of lines with take()

const res = Enumerable
    .from(purchase)
    .take(2)
    .toArray();

/*
[
  { event_date: '2023-01-01', user_id: 1, sales: 100 },
  { event_date: '2023-01-01', user_id: 1, sales: 200 }
]
*/
console.log(res);

DISTINCT

distinct()
- Remove duplicate lines
- If a key is specified as the first argument, retrieve only one line matching the key.

`COUNT(DISTINCT)` Implementation Example

count the number of lines after distinct(row=>row.column) with count()

const res = Enumerable
    .from(purchase)
    .groupBy(row => row.event_date,
        null,
        (key, rows) => {
            return {
                event_date: key,
                unique_user: rows.distinct((row: any) => row.user_id).count()
            };
        }
    )
    .toArray();

/*
[
  { event_date: '2023-01-01', unique_user: 2 },
  { event_date: '2023-01-02', unique_user: 1 }
]
*/
console.log(res);

Get the first line of each group of `GROUP BY`

Sort elements in groups by orderBy()
Get the first line with first()

const res = Enumerable
    .from(purchase)
    .groupBy(row => row.event_date
        , null
        , (key, rows) => {
            const row:any = rows
                .orderByDescending((row:any) => row.sales)
                .first();
            return row;
        }
    )
    .toArray();

/*
[
  { event_date: '2023-01-01', user_id: 2, sales: 1000 },
  { event_date: '2023-01-02', user_id: 999, sales: 10000 }
]
*/
console.log(res);

Other Functions

sum(row=>row.column)
average(row=>row.column)
max(row=>row.column)
min(row=>row.column)
count()

Note that null values are allowed.

document

Check the functionality in the Enumerable reference of the original LINQ
- https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable?view=net-7.0
There is no reference in linq in JavaScript, so look directly at the type definition file linq.d.ts to see what functions are available.
- https://github.com/mihaifm/linq

Reference Articles

https://medium.com/swlh/data-manipulation-in-javascript-using-linq-f3759e00aceb

Impressions, etc.

If you ask me if it is SQL-like, there are many unique aspects.

Since it can only be written in mesot syntax, it is difficult to understand at first glance, but if you are aware of the Enumerable chain, it will be easier to understand.

As you get used to it, you can do more complex things and it becomes more fun, but it becomes more and more distant from SQL, so we will keep it to SQL-like usage.

Welcome to new things

[Technical] [Electronic work] [Gadget] [Game] memo writing

I want to handle JavaScript arrays in SQL.

What is LINQ?

install

Basic usage

explanation

List of usage

sample data

select()

Output as is

where()

Multiple calls

orderBy()

groupBy()

composite key

INNER JOIN

composite key

LEFT JOIN

groupJoin()

composite key

selectMany()

Implement LEFT JOIN using groupJoin() and selectMany()

CROSS JOIN

UNION ALL

LIMIT & TOP

DISTINCT

`COUNT(DISTINCT)` Implementation Example

Get the first line of each group of `GROUP BY`

Other Functions

document

Reference Articles

Impressions, etc.

What is LINQ?

install

Basic usage

explanation

List of usage

sample data

select()

Output as is

where()

Multiple calls

orderBy()

groupBy()

composite key

INNER JOIN

composite key

LEFT JOIN

groupJoin()

composite key

selectMany()

Implement LEFT JOIN using groupJoin() and selectMany()

CROSS JOIN

UNION ALL

LIMIT & TOP

DISTINCT

COUNT(DISTINCT) Implementation Example

Get the first line of each group of GROUP BY

Other Functions

document

Reference Articles

Impressions, etc.

Related Category Articles

`COUNT(DISTINCT)` Implementation Example

Get the first line of each group of `GROUP BY`