I am using Knex and nodejs with Sqlite3.
The table structure is as follows:
---------------------------
| Parent |
---------------------------
| - id (PK) |
| - name |
---------------------------
---------------------------
| Child |
---------------------------
| - id (PK) |
| - parentid |
| - temp |
---------------------------
The relation is 1 Parent - * Child relationship and therefore I am trying to fetch the data structure as below where data is an array of child objects:
[
{
"id": 1,
"name": "xczna3k8c\ti504u\t",
"data": null
},
{
"id": 2,
"name": "xduip60dlc1g7z",
"data": null
},
{
"id": 3,
"name": "g0tv3eir\t\t6kp2\ta77g",
"data": [
{
"id": 9,
"temp": "9"
},
{
"id": 10,
"temp": "10"
}
]
}
]
The query is as follows
db
.select('parent.id', 'parent.name', 'child.data')
.from('parent')
.leftJoin(
db
.select("parentid",db.raw("json_group_array( json_object ( 'id', id, 'temp', temp )) as data"))
.from("child")
.groupBy('parentid')
.as('child')
, function(){
this.on('parent.id','=', 'child.parentid')
}
)
.orderBy('parent.id')
But what I am getting is data as a string instead of JSON object.
[
{
"id": 1,
"name": "xczna3k8c\ti504u\t",
"data": null
},
{
"id": 2,
"name": "xduip60dlc1g7z",
"data": null
},
{
"id": 3,
"name": "g0tv3eir\t\t6kp2\ta77g",
"data": "[{\"id\":9,\"temp\":\"9\"},{\"id\":10,\"temp\":\"10\"}]"
}
]
What I have tried:
- I've found there's a
typeCastproperty, but that exists in mysql db and similar thing doesn't exist on sqlite. - Execute the query and fetch the records then loop over the records to check if
dataexists then parse the data on API. - Leave the parsing on to UI.
I was wondering if there's any configuration/chaining method I can put on the query itself.