How to convert cassandra map records from blob to text using cql?

Question

I have a table with following schema

PK" text,

"SK" text,

":attrs" map<text, blob>,

PRIMARY KEY ("PK", "SK")

I would like to get the string value of a record that I inserted into this table? Currently I am getting the hexadecimal values since it's a blob.

Something like this but I can't get the syntax right

select blobAsText(":attrs"['key_name']) from my_table

Answer 1

First, I don't even know if :attrs as a column name is supported having special characters other than underscore (_), but let's park that for now.

The blobAsText native cql function doesn't support collection column types as you're attempting to do here.

See here as an example, this below works:

token@cqlsh:baselines> create table if not exists so_blob_as(i int primary key, b blob);
token@cqlsh:baselines> insert into so_blob_as(i,b) values (1,textasblob('1'));
token@cqlsh:baselines> select * from so_blob_as ;

 i | b
---+------
 1 | 0x31

(1 rows)
token@cqlsh:baselines> select i, blobastext(b) from so_blob_as ;

 i | system.blobastext(b)
---+----------------------
 1 |                    1

(1 rows)

whereas, this is what will work with collections,

token@cqlsh:baselines> alter table so_blob_as add m_tb map<text,blob>;
token@cqlsh:baselines> desc so_blob_as ;

CREATE TABLE baselines.so_blob_as (
    i int PRIMARY KEY,
    b blob,
    m_tb map<text, blob>
)...

token@cqlsh:baselines> update so_blob_as set m_tb = {'1': textasblob('1')} where i = 1;
token@cqlsh:baselines> SELECT * FROM so_blob_as ;

 i | b    | m_tb
---+------+-------------
 1 | 0x31 | {'1': 0x31}

(1 rows)

>>>This below will work<<<
token@cqlsh:baselines> SELECT i,blobastext(b),blobastext(m_tb) FROM so_blob_as ;

 i | system.blobastext(b) | system.blobastext(m_tb)
---+----------------------+----------------------------------------------------
 1 |                    1 | \x00\x00\x00\x01\x00\x00\x00\x011\x00\x00\x00\x011

(1 rows)

If you want the value for a given key and convert that, you will need to do that at the application side. See latest Java driver docs here as an example.