Myujth

I have two simple lucid models, called Parent (table parents) and Child (table children). The children table has a JSON field we call jsoncolumn.

My parent model has a belongsToMany relationship to the model Child.

children() 
 return this.belongsToMany('App/Models/Child').pivotTable('parent_child');

From various parents, I want to fetch the value of the parent, plus the value contained within the field jsoncolumn, at the key key; for that I want to use the PostgreSQL operator ->. Using Adonis Lucid syntax, it gives the following code:

Parent.query().select('*')
 .with('children', builder => 
 builder.select(Database.raw("jsoncolumn->'key' AS foo"));
 ).fetch();

Problem, the following code does not work. Even if within a Database.raw clause, the operator is quoted... so the generated select is:

SELECT "children"."jsoncolumn->'key'"

Note that it does not allow as well to use any function. Anything that is not a column name, actually, will not work.

What would be the correct syntax in order to do that?

To make a query to JSON data type at MySQL Server you need to do in the following way

SELECT columnOne, jsonColumn->'$.key' as Data FROM TableName;

I mean you have an incorrect sintax since you dont use the $. sign before the builder.select(Database.raw("jsoncolumn->'$.key' AS foo"));

Why?

The short sintax you're using requires it as my previous exaples shows to you