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

JeanLucAubert
May 3, 2023
322 views
0 votes
2 Answers

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,

Answers

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]
        }
    ]
}

- ErickRamirez
- May 4, 2023 at 12:35 pm
- 0 votes
0
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!
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.