Cassandra query by a field in JSON

Question

I'm using the latest cassandra version and trying to save JSON like below and was successful,

INSERT INTO mytable JSON '{"username": "myname", "country": "mycountry", "userid": "1"}'

Above query saves the record like,

"rows": [
        {
            "[json]": "{\"userid\": \"1\", \"country\": \"india\", \"username\": \"sai\"}"
        }
    ],
    "rowLength": 1,
    "columns": [
        {
            "name": "[json]",
            "type": {
                "code": 13,
                "type": null
            }
        }
    ]

Now I would like to retrieve the record based on userid:

SELECT JSON * FROM mytable WHERE userid = fromJson("1") // but this query throws error

All this occurs in a node/express app and I'm using dse-driver as the client driver.

Answer 1

The CQL command worked like below,

SELECT JSON * FROM mytable WHERE userid="1";

However if it has to be executed via the dse-driver then the below snippet worked,

let query = 'SELECT JSON * FROM mytable WHERE userid = ?';
client.execute(query, ["1"], { prepare: true });

where client is,

const dse    = require('dse-driver');
const client = new dse.Client({ 
              contactPoints: ['h1', 'h2'],
              authProvider: new dse.auth.DsePlainTextAuthProvider('username', 'pass')
});