USER MANUALS

Requests with Input Compound Values

The Denodo web services provide two ways of adding a condition by a compound field:

  1. Using the regular syntax of VQL, which is something like this:

SELECT ...
FROM V
WHERE input_array_field = { ROW( 1, NULL, 2, NULL, 'a', true, NULL, NULL, NULL
                            , NULL, NULL, NULL, NULL ) }

The URL below uses this syntax to indicate a condition with the array field input_array_field:

http://acme:9090/denodo-restfulws/.../views/V?input_array_field={ ROW( 1, NULL, 2, NULL, 'a', true, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) }

The value { ROW( 1, NULL, 2, NULL, 'a', true, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) } represents an array of a register. The braces surrounding ROW(...) indicate that the value is an array. Without the braces, it would be a register.

The section Management of Compound Values of the VQL Guide describes this syntax.

  1. The other syntax to express conditions with compound values is using a JSON document. For example, if we have a view like this:

CREATE OR REPLACE VIEW v1 AS
    SELECT 94301 AS zipcode,
           'ULT' AS ref
    UNION
    SELECT 94303 AS zipcode,
           'JPZ' AS parcelle;

CREATE OR REPLACE VIEW view_with_an_array AS
    SELECT nest(zipcode, ref) AS location
    FROM v1;

The following query has a condition that matches the array that this view returns:

SELECT *
FROM view_with_an_array
WHERE location = { ROW (94301, 'ULT'), ROW (94303, 'JPZ') };

To create the same filter in the REST web service, add the parameter $filter with the following value:

location = '[{"zipcode":94301, "ref": "ULT"}, {"zipcode":94303, "ref": "JPZ"}]'.

Adding this parameter, the URL will look like this:

http://acme:9090/denodo-restfulws/admin/views/view_with_an_array?$filter=location='[{"zipcode":94301, "ref": "ULT"}, {"zipcode":94303, "ref": "JPZ"}]'

Important

The URL above is not escaped to make the examples clearer. However, take into account that the HTTP clients have to escape the values of all the parameters. It is important to encode the URL correctly, this would be the previous URL encoded:

http://acme:9090/denodo-restfulws/admin/views/view_with_an_array?$filter=location%20%3D%20%27%5B%7B%22zipcode%22%3A94301%2C%20%22ref%22%3A%20%22ULT%22%7D%2C%20%7B%22zipcode%22%3A94303%2C%20%22ref%22%3A%20%22JPZ%22%7D%5D%27

Restrictions of the JSON syntax

  • You have to provide the JSON document in the $filter parameter, not as a value of the field. I.e. ?field_name='<json document>' does not work.

  • Make sure you surround the JSON document in single quotes and you escape the single quotes in the JSON document. To escape a single quote, prefix it with another single quote.

Add feedback