How to Query MySQL JSON Field Values with a Loopback REST API Filter

What's the proper Loopback filter format to query a value in a MySQL (5.7) JSON field? For example, how would we perform this query using a Loopback REST filter?

QUERY

SELECT name, info->"$.id", info->"$.team"
     FROM employee
     WHERE JSON_EXTRACT(info, "$.team") = "A"
     ORDER BY info->"$.id";

SAMPLE DATA

+---------------------------+----------
| info                      | name
+---------------------------+---------
| {"id": "1", "team": "A"}  | "Sam"  
| {"id": "2", "team": "A"}  | "Julie"
| {"id": "3", "name": "B"}  | "Rob"  
| {"id": "4", "name": "B"}  | "Cindy"
+---------------------------+---------

UNSUCCESSFUL ATTEMPTS

/employee?filter[where][info->$.team]=A
/employee?filter[where][info.team]=A
/employee?filter[where][info][team]=A

Solution

Since this question is the first to pop from Google search, and that the loopback mysql connector still does not allow for json querying, I feel like a proper answer should be given for future readers.

The work around is to add the feature directly on the connector by yourself, until the loopback politics settle on a decision on how to really handle it.

Here's our take: put this inside your /connectors folder:

import * as connector from "loopback-connector-mysql"

var g = require('strong-globalize')();
var SqlConnector = require('loopback-connector').SqlConnector;
var ParameterizedSQL = SqlConnector.ParameterizedSQL;


const _buildExpression = connector.MySQL.prototype.buildExpression;

connector.MySQL.prototype.buildExpression = function (columnName, operator, operatorValue, propertyDefinition) {
  if (operator === 'json') {
    operatorValue = JSON.parse(operatorValue);
    const keys = Object.keys(operatorValue);
    if (keys.length > 1) {
      g.warn('{{MySQL}} {{json}} syntax can only receive one key, received ' + keys.length);
    }
    const jsonPointer = "$." + keys[0];
    const value = operatorValue[keys[0]];
    const column = `JSON_EXTRACT(${columnName}, "${jsonPointer}")`;
    if (value && value.constructor === Object) {
      // this includes another operator, apply it on the built column
      const operator = Object.keys(value)[0];
      return _buildExpression.apply(this, [column, operator, value[operator], propertyDefinition]);
    }
    const clause = `${column} = ?`;
    return new ParameterizedSQL(clause,
      [value]);
  } else {
    return _buildExpression.apply(this, [columnName, operator, operatorValue, propertyDefinition])
  }
};

export default connector;

Then point to this file in your database config connector: 'connectors/mysql-json' or require it into the connector if that doesn't work (doc says we can define a path but we could not get it working...)

This is pretty simple, we overwrite the buildExpression function to be able to insert a new operator json. This will make it usable anywhere you'd use other operators such as gt, lt, nin etc.

We went a step further and allow operators be passed in your json operator, to be able to leverage them too.

Here's an example query filter:

{"where":
   {
      "jsonProperty":{"json":{"id":1}}
   }
}
// Results in 
// WHERE JSON_EXTRACT('jsonProperty', '$.id') = 1



{"where":
   {
      "jsonProperty":{"json":{"id":{"gt":1}}}
   }
}
// Results in 
// WHERE JSON_EXTRACT(`jsonProperty`, '$.id') > 1

We simply prepend the key of the object passed to json with $. for ease of use (not sure if it's the best but works for us). You could write any json path as the key, just ommit the $.