Can't you use the WHERE statement?

[opvexe]

func main() {
    jsonString := `
{
  "deviceInfo": {
    "deviceName": "设备3",
    "edgeDeviceName": "设备three",
    "topic": "GG2",
    "encryption": false,
    "compression": false
  },
  "telemetry": {
    "ts": 1585194439000,
    "aa": 6,
    "bb": 10,
    "OPC温度": 10370,
    "OPC湿度": "86",
    "OPC电量": true,
    "staType":0
  }
}
`
    trdsql.EnableDebug()
    r := bytes.NewBufferString(jsonString)
    importer, err := trdsql.NewBufferImporter("test", r, trdsql.InFormat(trdsql.JSON))
    if err != nil {
        log.Fatal(err)
    }
    writer := trdsql.NewWriter(trdsql.OutFormat(trdsql.VF))
    trd := trdsql.NewTRDSQL(importer, trdsql.NewExporter(writer))

    err = trd.Exec("SELECT telemetry FROM test where telemetry.aa > 3")
    if err != nil {
        log.Fatal(err)
    }
}

Out :

2021/10/20 10:54:13 driver: sqlite3, dsn: 
2021/10/20 10:54:13 CREATE TEMPORARY TABLE test ( `deviceInfo` text, `telemetry` text );
2021/10/20 10:54:13 INSERT INTO test (`deviceInfo`, `telemetry`) VALUES (?,?);
2021/10/20 10:54:13 SELECT telemetry FROM test where telemetry.aa > 3
2021/10/20 10:54:13 export: no such column: telemetry.aa [SELECT telemetry FROM test where telemetry.aa > 3]
exit status 1

[noborus]

json interprets the first item as a column. In this case the columns are "deviceInfo" and "telemetry".

The items in it need to be accessed by the JSON function. You can use the following methods of sqlite3 functions.

SELECT telemetry FROM test WHERE json_extract (telemetry,'$ .aa')> 3

go run  -tags='json1' ./main.go                                                                                            
2021/10/20 12:08:26 driver: sqlite3, dsn: 
2021/10/20 12:08:26 CREATE TEMPORARY TABLE test ( `telemetry` text, `deviceInfo` text );
2021/10/20 12:08:26 INSERT INTO test (`telemetry`, `deviceInfo`) VALUES (?,?);
2021/10/20 12:08:26 SELECT telemetry FROM test WHERE json_extract(telemetry, '$.aa') > 3
---[ 1]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  telemetry | {"OPC温度":10370,"OPC湿度":"86","OPC电量":true,"aa":6,"bb":10,"staType":0,"ts":1585194439000}

[opvexe]

json interprets the first item as a column. In this case the columns are "deviceInfo" and "telemetry".

The items in it need to be accessed by the JSON function. You can use the following methods of sqlite3 functions.

SELECT telemetry FROM test WHERE json_extract (telemetry,'$ .aa')> 3

go run  -tags='json1' ./main.go                                                                                            
2021/10/20 12:08:26 driver: sqlite3, dsn: 
2021/10/20 12:08:26 CREATE TEMPORARY TABLE test ( `telemetry` text, `deviceInfo` text );
2021/10/20 12:08:26 INSERT INTO test (`telemetry`, `deviceInfo`) VALUES (?,?);
2021/10/20 12:08:26 SELECT telemetry FROM test WHERE json_extract(telemetry, '$.aa') > 3
---[ 1]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  telemetry | {"OPC温度":10370,"OPC湿度":"86","OPC电量":true,"aa":6,"bb":10,"staType":0,"ts":1585194439000}

Thank you very much for solving a big problem that has been bothering me.

[opvexe]

2021年10月20日 上午11:16，Noboru Saito @.***> 写道：

json interprets the first item as a column. In this case the columns are "deviceInfo" and "telemetry".

The items in it need to be accessed by the JSON function. You can use the following methods of sqlite3 functions.

SELECT telemetry FROM test WHERE json_extract (telemetry,'$ .aa')> 3 go run -tags='json1' ./main.go
2021/10/20 12:08:26 driver: sqlite3, dsn: 2021/10/20 12:08:26 CREATE TEMPORARY TABLE test ( telemetry text, deviceInfo text ); 2021/10/20 12:08:26 INSERT INTO test (telemetry, deviceInfo) VALUES (?,?); 2021/10/20 12:08:26 SELECT telemetry FROM test WHERE json_extract(telemetry, '$.aa') > 3 ---[ 1]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ telemetry | {"OPC温度":10370,"OPC湿度":"86","OPC电量":true,"aa":6,"bb":10,"staType":0,"ts":1585194439000} — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/noborus/trdsql/issues/164#issuecomment-947285565, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGNGHC2AD6NRYVQKJ5U4M3DUHYYCRANCNFSM5GKRVJGA. Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

May I ask you a question?

Sql：

select *,telemetry as model_data, telemetry .ts as model_data.timeStamp

Input:

{ "deviceInfo": { "deviceName": "设备3", "edgeDeviceName": "设备three", "topic": "GG2", "encryption": false, "compression": false }, "telemetry": { "ts": 1585194439000, "aa": 6, "bb": 10, "OPC温度": 10370, "OPC湿度": "86", "OPC电量": true, "staType":0 } }

Output:

{ "deviceInfo": { "deviceName": "设备3", "edgeDeviceName": "设备three", "topic": "GG2", "encryption": false, "compression": false }, “Model_data": { "timeStamp": 1585194439000, "ts": 1585194439000, "aa": 6, "bb": 10, "OPC温度": 10370, "OPC湿度": "86", "OPC电量": true, "staType":0 }, "telemetry": { "ts": 1585194439000, "aa": 6, "bb": 10, "OPC温度": 10370, "OPC湿度": "86", "OPC电量": true, "staType":0 } }

[noborus]

It's complicated because it uses SQL JSON functions. You need to use a combination of json_set and json_extract. In some cases json_remove too.

SELECT
 deviceinfo,
 json_set(telemetry,'$.timestamp',json_extract(telemetry,'$.ts')) as Model_data,
 telemetry 
 FROM test

This alone is not very suitable for SQL :-)

[opvexe]

I took a look at the SQLite Json documentation and solved the problem with Json inserts. Seeing your reply, I am still very happy that you can help me solve it.

err = trd.Exec("select *,telemetry,json_insert (telemetry,'$.timeStamp',json_extract (telemetry,'$.ts')) as model_data from test")