MariaDB COLUMN_JSON query returns binary

Question

I've been trying to use dynamic columns with an instance of MariaDB v10.1.12.

First, I send the following query:

INSERT INTO savedDisplays (user, name, body, dataSource, params) VALUES ('Marty', 'Hey', 'Hoy', 'temp', COLUMN_CREATE('type', 'tab', 'col0', 'champions', 'col1', 'averageResults'));

Where params' type was defined as a blob, just like the documentation suggests. The query is accepted, the table updated. If I COLUMN_CHECK the results, it tells me it's fine.

But when I try to select:

"SELECT COLUMN_JSON(params) AS params FROM savedDisplays;

I get a {type: "Buffer", data: Array} containing binary returned to me, instead of the {"type":"tab", "col0":"champions", "col1":"averageResults"} I expect.

EDIT: I can use COLUMN_GET just fine, but I need every column inside the params field, and I need to check the type property first to know what kind of and how many columns there are in the JSON / params field. I could probably make it work still, but that would require multiple queries, as opposed to only one.

Any ideas?

Michael Piefel · Accepted Answer · 2016-09-27 11:19:37Z

2

Try:

SELECT CONVERT(COLUMN_JSON(params) USING utf8) AS params FROM savedDisplays

In MariaDB 10 this works at every table:

SELECT CONVERT(COLUMN_JSON(COLUMN_CREATE('t', text, 'v', value)) USING utf8)
    as json FROM test WHERE 1 AND value LIKE '%12345%' LIMIT 10;

output in node.js

[ TextRow { json: '{"t":"test text","v":"0.5339044212345805"}' } ]

edited Sep 27, 2016 at 11:19

Michael Piefel

20.2k13 gold badges92 silver badges124 bronze badges

answered Sep 27, 2016 at 10:24

webolizzer

3352 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MariaDB COLUMN_JSON query returns binary

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related