CODEWITHSUNDEEP
mysqljson
Dally
Dally

Reputation: 1427

MySQL - Escaping double quotes and brackets when querying JSON column

how do you escape double quotes and brackets when querying a JSON column containing an array of objects?

This works when I run it... 

SELECT boarded, boarded->>"$[0].division.id" AS divisionId FROM onboarder

But this doesn't...

SELECT boarded, boarded->>"$[*].division.id" AS divisionId FROM onboarder

I thought double arrows escaped everything and bought back only the value. This is what I have...

enter image description here

Upvotes: 0

Views: 2868

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

The ->> operator does not escape anything. It just converts the result to a scalar data type, like text or integer. It's the equivalent of doing JSON_UNQUOTE(JSON_EXTRACT(...)):

Unquotes JSON value and returns the result as a utf8mb4 string.

We can demonstrate the difference between -> and ->> by using the result set to create a new table, and inspecting the data types it creates.

create table t as SELECT boarded, boarded->"$[*].division.id" AS divisionId FROM onboarder;

show create table t;
CREATE TABLE `t` (
  `boarded` json DEFAULT NULL,
  `divisionId` json DEFAULT NULL
);

select * from t;
+------------------------------------+--------------+
| boarded                            | divisionId   |
+------------------------------------+--------------+
| [{"division": {"id": "8ac7a..."}}] | ["8ac7a..."] |
+------------------------------------+--------------+

Note divisionId is a json document, which is an array.

If we use ->> this is what happens:

create table t as SELECT boarded, boarded->>"$[*].division.id" AS divisionId FROM onboarder;

show create table t;

CREATE TABLE `t` (
  `boarded` json DEFAULT NULL,
  `divisionId` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
)

select * from t;
+------------------------------------+--------------+
| boarded                            | divisionId   |
+------------------------------------+--------------+
| [{"division": {"id": "8ac7a..."}}] | ["8ac7a..."] |
+------------------------------------+--------------+

There's no visible difference, because the square brackets are still present. But the latter is stored as a longtext data type.

Re your comment:

How can I return divisionId as a value so that it's not an array and quoted?

You used $[*] in your query, and the meaning of this pattern is to return all elements of the array, as an array. To get a single value, you need to query a single element of the array, as in your first example:

boarded->>'$[0].division.id'

This would be so much easier if you didn't use JSON, but stored your data in a traditional table, with one division on its own row, and each field of the division in its own column.

CREATE TABLE divisions (
  division_id VARCHAR(...) PRIMARY KEY,
  merchant ...
);

CREATE TABLE merchants (
  merchant_id ... PRIMARY KEY,
  division_id VARCHAR(...),
  FOREIGN KEY (division_id) REFERENCES division(division_id)
);

The more examples I see of how developers try to use JSON in MySQL, the more I'm convinced it's a bad idea.

Upvotes: 3

Related Questions