1

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:

  1. I've found there's a typeCast property, but that exists in mysql db and similar thing doesn't exist on sqlite.
  2. Execute the query and fetch the records then loop over the records to check if data exists then parse the data on API.
  3. Leave the parsing on to UI.

I was wondering if there's any configuration/chaining method I can put on the query itself.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.