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"

score 2 · Accepted Answer · answered Nov 27 '14 at 10:38

2

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;

answered Nov 27 '14 at 10:38

Hannes Mühleisen

1

Thanks, the most important part is: "build it from source", because I used win version, that did not support json. – hjortron Nov 27 '14 at 19:38
Can I use JSON type as a UDF input parameter? Thanks – GBrian Sep 16 '15 at 03:53

score 2 · Answer 2 · answered Dec 15 '16 at 01:51

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'));

2 Answers2