Getting a JSON key as column with node mysql2

Question

Try to make the following SQL query using the node mysql2 package:

SELECT name, someObject->>"$.foo" as slice
FROM someTable;

The table someTable has 2 columns: name, a varchar; and someObject, a JSON column.

Imagine at least one row exists like this:

name      someObject
==========================
John      { "foo": "bar" }

The expected result of the query is:

name      slice
==========================
John      bar

Simply doing this works:

const result = await connection.query('SELECT name, someObject->>"$.foo" as slice FROM someTable');

However, I need the foo value escaped as it is user input. How to properly escape this?

Coo · Accepted Answer · 2021-08-02 05:48:43Z

1

Writing the question, I figured out the answer. Hopefully this can be helpful to others.

Considering the column / slice string: someObject->>"$.foo"

The answer is: Treat the column portion (someObject) as a column, and treat the string portion ($.foo) as a string.

So to escape it properly do this:

const columnName = "someObject";
const sliceName = "$.foo";

const sql = 'SELECT name, ??->>? as slice FROM someTable';
const values = [columnName, sliceName];
const result = await connection.query(sql, values);

answered Aug 2, 2021 at 5:48

Coo

1,8923 gold badges20 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting a JSON key as column with node mysql2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related