Application: gvSIG desktop

    public static final String FUNCTION_JSON_VALUE = "JSON_VALUE";

import java.util.logging.Level;

import java.util.logging.Logger;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.apache.commons.io.FilenameUtils;

//        InputStream is = null;

//            is = url.openStream();

//            List<String> lines = IOUtils.readLines(is);

//            json = new JSONObject(StringUtils.join(lines,  "\n"));

            json = new JSONObject(IOUtils.toString(url));

//        } finally {

//            IOUtils.closeQuietly(is);

            this.description = process_includes(url, this.description);

    private String process_includes(URL pathname, String text) {

        Pattern p1 = Pattern.compile("[<][%]include (?<fname>[a-zA-Z0-9_$.]+)[%][>]", Pattern.DOTALL);

        while(true) {

            Matcher m = p1.matcher(text);

            if( m==null || !m.find()) {

                return text;

            }

            String path = FilenameUtils.getPathNoEndSeparator(pathname.toString());

            String fname = m.group("fname");

            URL url;

            String replacement;

            try {

                url = new URL(path+"/"+fname);

                replacement = IOUtils.toString(url);

//                text = m.replaceAll(replacement);

                StringBuilder builder = new StringBuilder(text.length()+replacement.length());

                builder.append(text.substring(0, m.start()));

                builder.append(replacement);

                builder.append(text.substring(m.end()));

                text = builder.toString();

            } catch (Exception ex) {

                return text;

            }

        }

    }

{

    "name": "JSON_VALUE",

    "group": "Conversion",

    "template": "JSON_VALUE({{exp}}, path)",

    "args": [

        "exp - specify an expression that evaluates to a text literal. If expr is null, then the function returns null. If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns null by default.",

        "JsonPath - specify a JSON path expression. The function uses the path expression to evaluate expr and find a scalar JSON value that matches, or satisfies, the path expression. The path expression must be a text literal"

    ],

    "description": "Extracts a scalar value from a JSON string.<br>\n<%include JSON_VALUE.description.html%>",

    "returnType": "Object",

    "sqlCompatible": false

}

<p>JsonPath expressions always refer to a

JSON structure in the same way as XPath

expression are used in combination

with an XML document. The "root member

object" in JsonPath is always referred to

as <code>$</code> regardless if it is an

object or array.</p>

<p>JsonPath expressions can use the

dot–notation</p>

<p><code>$.store.book[0].title</code></p>

<p>or the bracket–notation</p>

<p><code>$['store']['book'][0]['title']</code></p>

<h2>Operators</h2>

<table>

<thead>

<tr>

<th align="left">Operator</th>

<th align="left">Description</th>

</tr>

</thead>

<tbody>

<tr>

<td align="left"><code>$</code></td>

<td align="left">The root element to

query. This starts all path

expressions.</td>

</tr>

<tr>

<td align="left"><code>@</code></td>

<td align="left">The current node

being processed by a filter

predicate.</td>

</tr>

<tr>

<td align="left"><code>*</code></td>

<td align="left">Wildcard. Available

anywhere a name or numeric are

required.</td>

</tr>

<tr>

<td align="left"><code>..</code></td>

<td align="left">Deep scan. Available

anywhere a name is required.</td>

</tr>

<tr>

<td align="left"><code>.&lt;name&gt;</code></td>

<td align="left">Dot-notated child</td>

</tr>

<tr>

<td align="left"><code>['&lt;name&gt;'

(, '&lt;name&gt;')]</code></td>

<td align="left">Bracket-notated child

or children</td>

</tr>

<tr>

<td align="left"><code>[&lt;number&gt;

(, &lt;number&gt;)]</code></td>

<td align="left">Array index or

indexes</td>

</tr>

<tr>

<td align="left"><code>[start:end]</code></td>

<td align="left">Array slice operator</td>

</tr>

<tr>

<td align="left"><code>[?(&lt;expression&gt;)]</code></td>

<td align="left">Filter expression. Expression must evaluate to a boolean value.</td>

</tr>

</tbody>

</table>

<h2>Functions</h2>

<p>Functions can be invoked at the tail end

of a path - the input to a function is the

output of the path expression.

The function output is dictated by the

function itself.</p>

<table>

<thead>

<tr>

<th align="left">Function</th>

<th align="left">Description</th>

<th align="left">Output type</th>

</tr>

</thead>

<tbody>

<tr>

<td align="left">min()</td>

<td align="left">Provides the min value of an array of numbers</td>

<td align="left">Double</td>

</tr>

<tr>

<td align="left">max()</td>

<td align="left">Provides the max value of an array of numbers</td>

<td align="left">Double</td>

</tr>

<tr>

<td align="left">avg()</td>

<td align="left">Provides the average value of an array of numbers</td>

<td align="left">Double</td>

</tr>

<tr>

<td align="left">stddev()</td>

<td align="left">Provides the standard deviation value of an array of numbers</td>

<td align="left">Double</td>

</tr>

<tr>

<td align="left">length()</td>

<td align="left">Provides the length of an array</td>

<td align="left">Integer</td>

</tr>

<tr>

<td align="left">sum()</td>

<td align="left">Provides the sum value of an array of numbers</td>

<td align="left">Double</td>

</tr>

<tr>

<td align="left">keys()</td>

<td align="left">Provides the property keys (An alternative for terminal tilde <code>~</code>)</td>

<td align="left"><code>Set&lt;E&gt;</code></td>

</tr>

<tr>

<td align="left">concat(X)</td>

<td align="left">Provides a concatinated version of the path output with a new item</td>

<td align="left">like input</td>

</tr>

<tr>

<td align="left">append(X)</td>

<td align="left">add an item to the json path output array</td>

<td align="left">like input</td>

</tr>

</tbody>

</table>

<h2>Filter Operators</h2>

<p>Filters are logical expressions used to

filter arrays. A typical filter would be <code>[?(@.age&gt; 18)]</code> where <code>@</code>

represents the current item being

processed. More complex filters can be

created with logical operators <code>&amp;&amp;</code>

and <code>||</code>. String literals must

be enclosed by single or double quotes (<code>[?(@.color

== 'blue')]</code> or <code>[?(@.color

== "blue")]</code>).</p>

<table>

<thead>

<tr>

<th align="left">Operator</th>

<th align="left">Description</th>

</tr>

</thead>

<tbody>

<tr>

<td align="left">==</td>

<td align="left">left is equal to right (note that 1 is not equal to '1')</td>

</tr>

<tr>

<td align="left">!=</td>

<td align="left">left is not equal to right</td>

</tr>

<tr>

<td align="left">&lt;</td>

<td align="left">left is less than right</td>

</tr>

<tr>

<td align="left">&lt;=</td>

<td align="left">left is less or equal to right</td>

</tr>

<tr>

<td align="left">&gt;</td>

<td align="left">left is greater than right</td>

</tr>

<tr>

<td align="left">&gt;=</td>

<td align="left">left is greater than or equal to right</td>

</tr>

<tr>

<td align="left">=~</td>

<td align="left">left matches regular expression [?(@.name =~ /foo.*?/i)]</td>

</tr>

<tr>

<td align="left">in</td>

<td align="left">left exists in right [?(@.size in ['S', 'M'])]</td>

</tr>

<tr>

<td align="left">nin</td>

<td align="left">left does not exists in right</td>

</tr>

<tr>

<td align="left">subsetof</td>

<td align="left">left is a subset of right [?(@.sizes subsetof ['S', 'M', 'L'])]</td>

</tr>

<tr>

<td align="left">anyof</td>

<td align="left">left has an intersection with right [?(@.sizes anyof ['M', 'L'])]</td>

</tr>

<tr>

<td align="left">noneof</td>

<td align="left">left has no intersection with right [?(@.sizes noneof ['M', 'L'])]</td>

</tr>

<tr>

<td align="left">size</td>

<td align="left">size of left (array or string) should match right</td>

</tr>

<tr>

<td align="left">empty</td>

<td align="left">left (array or string) should be empty</td>

</tr>

</tbody>

</table>

<h2>Path Examples</h2>

<p>Given the json</p>

<pre>

{

    "store": {

        "book": [

            {

                "category": "reference",

                "author": "Nigel Rees",

                "title": "Sayings of the Century",

                "price": 8.95

            },

            {

                "category": "fiction",

                "author": "Evelyn Waugh",

                "title": "Sword of Honour",

                "price": 12.99

            },

            {

                "category": "fiction",

                "author": "Herman Melville",

                "title": "Moby Dick",

                "isbn": "0-553-21311-3",

                "price": 8.99

            },

            {

                "category": "fiction",

                "author": "J. R. R. Tolkien",

                "title": "The Lord of the Rings",

                "isbn": "0-395-19395-8",

                "price": 22.99

            }

        ],

        "bicycle": {

            "color": "red",

            "price": 19.95

        }

    },

    "expensive": 10

}

</pre>

<table>

<thead>

<tr>

<th align="left">JsonPath</th>

<th align="left">Result</th>

</tr>

</thead>

<tbody>

<tr>

<td align="left">$.store.book[*].author</td>

<td align="left">The authors of all books</td>

</tr>

<tr>

<td align="left">$..author</td>

<td align="left">All authors</td>

</tr>

<tr>

<td align="left">$.store.*</td>

<td align="left">All things, both books and bicycles</td>

</tr>

<tr>

<td align="left">$.store..price</td>

<td align="left">The price of everything</td>

</tr>

<tr>

<td align="left">$..book[2]</td>

<td align="left">The third book</td>

</tr>

<tr>

<td align="left">$..book[-2]</td>

<td align="left">The second to last book</td>

</tr>

<tr>

<td align="left">$..book[0,1]</td>

<td align="left">The first two books</td>

</tr>

<tr>

<td align="left">$..book[:2]</td>

<td align="left">All books from index 0 (inclusive) until index 2 (exclusive)</td>

</tr>

<tr>

<td align="left">$..book[1:2]</td>

<td align="left">All books from index 1 (inclusive) until index 2 (exclusive)</td>

</tr>

<tr>

<td align="left">$..book[-2:]</td>

<td align="left">Last two books</td>

</tr>

<tr>

<td align="left">$..book[2:]</td>

<td align="left">Book number two from tail</td>

</tr>

<tr>

<td align="left">$..book[?(@.isbn)]</td>

<td align="left">All books with an ISBN number</td>

</tr>

<tr>

<td align="left">$.store.book[?(@.price&lt; 10)]</td>

<td align="left">All books in store cheaper than 10</td>

</tr>

<tr>

<td align="left">$..book[?(@.price&lt;= $['expensive'])]</td>

<td align="left">All books in store that are not "expensive"</td>

</tr>

<tr>

<td align="left">$..book[?(@.author=~ /.*REES/i)]</td>

<td align="left">All books matching regex (ignore case)</td>

</tr>

<tr>

<td align="left">$..*</td>

<td align="left">Give me every thing</td>

</tr>

<tr>

<td align="left">$..book.length()</td>

<td align="left">The number of books</td>

</tr>

</tbody>

</table>

<br>

<p>You can consult the documentation of the JSON_VALUE function of Oracle and SQL Server for more information.</p>

        //

        // function docs at:

        // Oracle json_value: https://docs.oracle.com/database/121/SQLRF/functions093.htm#SQLRF56668

        // SQLServer json_value: https://docs.microsoft.com/es-es/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

        // PostgreSQL jsonb_path_query: https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

        // The implementation used is:

        // https://github.com/json-path/JsonPath

        //

                "finds a specified scalar JSON value in JSON data and returns it.",

            FUNCTION_JSON_VALUE+"({{expr}}, JsonPath)",

            new String[]{

                "expr - specify an expression that evaluates to a text literal.",

                "JsonPath - specify a JSON path expression."

            }

        );

        JsonPathContext context;

        try {

            context = manager.createJSonPathContext(json);

        } catch(Exception ex) {

            return null;

        }

        return value;  

    }

import org.gvsig.expressionevaluator.impl.function.typeconversion.JsonValueFunction;

        this.addFunction(new JsonValueFunction());

            if( !StringUtils.containsAny(functionDescription, "<br>", "<p>", "<br/>")) {

                html.append(functionDescription.replace("\n", "<br>")).append("<br>\n");

            } else {

                html.append(functionDescription).append("<br>\n");

            }