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

Question

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

Salketer · Accepted Answer · 2020-08-10 13:36:03Z

2

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 $.

edited Aug 10, 2020 at 13:36

answered Aug 10, 2020 at 13:24

Salketer

15.8k2 gold badges32 silver badges64 bronze badges

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

2 Comments

user257980 Over a year ago

This looks interesting. Did you had any issues? Looking similar solution for our Loopback 3 instances. They store customer specific models to json object.

Salketer Over a year ago

So far this worked pretty good. We had to make some adjustments like not adding the $. automatically to support other syntaxes and use a different JSON method than extract like JSON_SEARCH depending on the operator used... Also, we had problems having the filter transformed into [object Object] on some queries, turns out we need to use the option "allowExtendedOperators" or else the $json operator wasn't properly resolved and the property was coherced using toString()

Jaime Franco · Accepted Answer · 2016-06-07 18:21:46Z

1

I don't think this is possible for the moment, i think that neither the connector support the JSON_EXTRACT.

https://github.com/strongloop/loopback-connector-mysql

I think that for execute that kind of queries you may use raw queries and write custom code using the actual connector.

answered Jun 7, 2016 at 18:21

Jaime Franco

493 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related