How do I post a JSON object into a CQL map column?

Question

New with Cassandra, i would like to store some datas in a table with UDT. So i create a UDT with a map column :

CREATE TYPE IF NOT EXISTS test.lifestyle (
    name text,
    values map<timestamp, int>
);

Then i create a table using this UDT :

CREATE TABLE IF NOT EXISTS test.account_lifestyle (
    account_id varchar PRIMARY KEY,
    life_styles frozen<set<lifestyle>>
);

Use a simple NestJS application to create an API, so, my models are :

import { LifeStyleModel } from './../life-style-model/life-style-model'
export class AccountLifeStyleModel {
    accountId: string
    lifeStyles: Set<LifeStyleModel>
}

And :

export class LifeStyleModel {
    name: string
    values: Map<number, number>
}

Finally i create a Repository from cassandra-driver :

@Injectable()
export class LifeStyleRepositoryService implements OnModuleInit {
    lifeStyleMapper: mapping.ModelMapper<AccountLifeStyleModel>

    private readonly mappingOptions: mapping.MappingOptions = {
        models: {
            'LifeStyle': {
                tables: [
                    'account_lifestyle'
                ],
                mappings: new mapping.UnderscoreCqlToCamelCaseMappings
            }
        }
    }

    constructor(private _dbService: DbService) {}

    onModuleInit() {
        this.lifeStyleMapper = this._dbService
            .createMapper(this.mappingOptions)
            .forModel('LifeStyle')
    }

    async getAll() {
        return (await this.lifeStyleMapper.findAll()).toArray()
    }

    async add(accountLifeStyle: AccountLifeStyleModel) {
        return (await this.lifeStyleMapper.insert(accountLifeStyle)).toArray()
    }
}

Then use Postman to test my endpoint passing this JSON :

{
    "accountId": "64578abc7859az",
    "lifeStyles": [
        {
            "name": "Sport",
            "values": [
                [1683099227, 1]
            ]
        },
        {
            "name": "Tobaco",
            "values": [
                [1683099227, 4]
            ]
        },
        {
            "name": "Alcohol",
            "values": [
                [1683099227, 4]
            ]
        }
    ]
}

When endpoint was reached, i got the following error :

[Nest] 9045  - 03/05/2023 17:39:54   ERROR [ExceptionsHandler] Expected Number, obtained [ 1683099227, 1 ]

So, i mean my JSON does not correspond to expected format of a map, but i don't know how to set the correct JSON or modify my models to pass correct JSON.

Any help would be appreciate.

Regards,

JL

score 2 · Answer 1 · answered May 03 '23 at 17:18

2

I think you have it set up incorrectly. Try this:

{
    "accountId": "64578abc7859az",
    "lifeStyles": [
        {
            "name": "Sport",
            "values": [1683099227, 1]
        },
        {
            "name": "Tobaco",
            "values": [1683099227, 4]
        },
        {
            "name": "Alcohol",
            "values": [1683099227, 4]
        }
    ]
}

answered May 03 '23 at 17:18

stevenlacerda

1,187
2
9
21

Thx, no more error message with this setup, but in the database, i got a strange response : "lifeStyles": [ { "name": "Alcohol", "values": { "Sat Jan 01 2000 00:00:00 GMT+0100 (heure normale d’Europe centrale)": 1683099227, "Mon Jan 01 2001 00:00:00 GMT+0100 (heure normale d’Europe centrale)": 4 } }, ... ] And i thaught that a Cassandra map had the same behavior as a JS Map and permit to store many couples, i think i'll refactor from map to tuple – Jean-Luc Aubert May 03 '23 at 17:26
The timestamps seems to be a little bit strange : `Sat Jan 01 2000 00:00:00 GMT+0100 (heure normale d’Europe centrale)` and `Mon Jan 01 2001 00:00:00 GMT+0100 (heure normale d’Europe centrale)` It's a wide interval for datas posted ! lol – Jean-Luc Aubert May 03 '23 at 17:56

score 1 · Accepted Answer · answered May 04 '23 at 10:35

The failure is due to your JSON being invalid. The values field is double-nested, for example:

            "values": [
                [1683099227, 1]
            ]

It should just be something like:

            "values": [1683099227, 1]

I have to add that looking at your dataset, I would discourage you from using UDTs because it adds a level of complexity that is unnecessary. Our recommendation is to use native CQL columns whenever possible because it makes it much easier to perform CRUD operations.

For example, you could model your table this way:

CREATE TABLE lifestyles_by_accountid (
    account_id text,
    name text,
    tstamp_col timestamp,
    int_col int,
    PRIMARY KEY(account_id, name)
)

With this data model, each account ID has one or more rows of name with each name having a corresponding tstamp and int_col. Using your example data, your table would look like:

 account_id     | name    | tstamp_col | int_col
----------------+---------+------------+---------
 64578abc7859az | Sport   | 1683099227 | 1
 64578abc7859az | Tobaco  | 1683099227 | 4
 64578abc7859az | Alcohol | 1683099227 | 4

With this model, you don't have to deal with the complexities of UDTs and CQL collections so it's easier to maintain your code. Cheers!

How do I post a JSON object into a CQL map column?

2 Answers2