How to use JSON in MonetDB

Question

MonetDB documentation says, that JSON is supported in MonetDB, but I can`t find any example. When I try to implement this query in MonetDB client:

   json.filter({ "store": {    
    "bicycle": {
      "color": "red",
      "price": 19.95
    }}},'$')    

I get this error: syntax error, unexpected IDENT in: "json"

Answer 1

From https://www.monetdb.org/pipermail/users-list/2014-November/007700.html:

In MonetDB, JSON is a column type, so you first need to define a table with a column of type JSON, something like:

CREATE TABLE a (js JSON);

Once you inserted JSON documents into the table, you can do a query like:

SELECT json.filter(js, 'store') FROM a;

Answer 2

Have you tried putting single quotes around the first param?

The following works for me.

select json.filter('{ "store": {
    "bicycle": {
      "color": "red",
      "price": 19.95
    }}}', '$');

The result is:

[{ "store": {
"bicycle": {
  "color": "red",
  "price": 19.95
}}}]

I also tried this:

select json.filter('{ "store": {
  "bicycle": {
    "color": "red",
    "price": 19.95
  }}}', '$.store.bicycle.color');

The result is:

["red"]

To return the colour as a string:

select json.text(json.filter('{ "store": {
  "bicycle": {
    "color": "red",
    "price": 19.95
  }}}', '$.store.bicycle.color'));

...I get:

red

If I try:

select json.text(json.filter('{ "store": {
  "bicycle": {
    "color": "red",
    "price": 19.95
  }}}', '$.store.bicycle.price'));

...I get back the price as a string:

19.95

But I can also get it back as a number:

select json.number(json.filter('{ "store": {
      "bicycle": {
        "color": "red",
        "price": 19.95
      }}}', '$.store.bicycle.price'));