jq for Apache Phoenix

0. To use UDFs, you have to add the following property to hbase-site.xml on both client and server.

   <property>
    <name>phoenix.functions.allowUserDefinedFunctions</name>
    <value>true</value>
   </property>

1. Build a UDF jar and copy it into your ${hbase.dynamic.jars.dir}.

   mvn clean package
   # adjust /hbase/lib to your ${hbase.dynamic.jars.dir}
   sudo -u hbase hadoop fs -copyFromLocal target/phoenix-jq-udf-0.0.1.jar /hbase/lib

2. Run CREATE FUNCTION.

   CREATE FUNCTION jq(VARCHAR, VARCHAR CONSTANT, BOOLEAN CONSTANT DEFAULTVALUE='FALSE') RETURNS VARCHAR AS 'net.thisptr.phoenix.udf.jsonquery.JsonQueryFunction';

Refer to User-defined functions (UDFs) official documentation for general information about UDFs.

jq(JSON, JQ, RAW=FALSE)

1. Each UPSERT statement adds an element to set of unique integers represented by a JSON array.

   > CREATE TABLE foo (id INTEGER NOT NULL, val VARCHAR, CONSTRAINT pk PRIMARY KEY (id));
   > UPSERT INTO foo (id, val) VALUES (1, '[1]') ON DUPLICATE KEY UPDATE val = jq(val, '. + [1] | unique_by(.)');
   > UPSERT INTO foo (id, val) VALUES (1, '[1]') ON DUPLICATE KEY UPDATE val = jq(val, '. + [1] | unique_by(.)');
   > UPSERT INTO foo (id, val) VALUES (1, '[2]') ON DUPLICATE KEY UPDATE val = jq(val, '. + [2] | unique_by(.)');

   > SELECT * FROM foo;
   +-----+--------+
   | ID  |  VAL   |
   +-----+--------+
   | 1   | [1,2]  |
   +-----+--------+

2. When RAW is set to TRUE, a raw string value is returned instead of its JSON representation (i.e. no quotes, no escapes).

   > CREATE TABLE foo (id INTEGER NOT NULL, val VARCHAR, CONSTRAINT pk PRIMARY KEY (id));
   > UPSERT INTO foo (id, val) VALUES (1, "\"foo\"");

   > SELECT id, jq(val, '.', TRUE) FROM foo;
   +-----+----------------------+
   | ID  | JQ(VAL, '.', true)   |
   +-----+----------------------+
   | 1   | foo                  |
   +-----+----------------------+

   > SELECT id, jq(val, '.') FROM foo;
   +-----+---------------+
   | ID  | JQ(VAL, '.')  |
   +-----+---------------+
   | 1   | "foo"         |
   +-----+---------------+

The Apache License, Version 2.0