yesoreyeram / uql Goto Github PK
View Code? Open in Web Editor NEWLicense: Apache License 2.0
License: Apache License 2.0
Trying to get data from a JSON source which contains a number of names and prices and date. New entries are added daily and old ones left for historical use. I am only interested in the most recent entry for each named item. There would be different number of entries each day, because not every value is updated daily. Also new names are added over time so "limit" can not work as I don't know how many entries there will be on any day.
attached sample as discussed
sample.txt
Using parse-csv
on a CSV source (in Grafana) using ;
as delimiter and ,
as decimal seperator import fails.
Is it possible to provide these parameters in a UQL query?
Hi all,
I was trying to parse this data:
<root>
<Device Name="PIKO 3.6-1 MP plus" Type="Inverter" Platform="Net16" HmiPlatform="HMI17" NominalPower="3600" UserPowerLimit="nan" CountryPowerLimit="nan" Serial="763168EH006917660002" OEMSerial="10351939" BusAddress="1" NetBiosName="INV006917660002" WebPortal="PIKO Solar Portal" ManufacturerURL="kostal-solar-electric.com" IpAddress="192.168.1.128" DateTime="2024-06-26T10:07:27" MilliSeconds="769">
<Measurements>
<Measurement Value="247.0" Unit="V" Type="AC_Voltage"/>
<Measurement Value="10.234" Unit="A" Type="AC_Current"/>
<Measurement Value="2520.8" Unit="W" Type="AC_Power"/>
<Measurement Value="2521.3" Unit="W" Type="AC_Power_fast"/>
<Measurement Value="50.068" Unit="Hz" Type="AC_Frequency"/>
<Measurement Value="623.2" Unit="V" Type="DC_Voltage"/>
<Measurement Value="4.289" Unit="A" Type="DC_Current"/>
<Measurement Value="624.4" Unit="V" Type="LINK_Voltage"/>
<Measurement Value="6439.1" Unit="W" Type="GridPower"/>
<Measurement Value="0.0" Unit="W" Type="GridConsumedPower"/>
<Measurement Value="6439.1" Unit="W" Type="GridInjectedPower"/>
<Measurement Value="0.0" Unit="W" Type="OwnConsumedPower"/>
<Measurement Value="70.0" Unit="%" Type="Derating"/>
</Measurements>
</Device>
</root>
Using UQL, I'm able to parse it and obtain this JSON:
parse-xml
| scope "root.Device.Measurements.Measurement"
[
{ "@_Value": "234.2", "@_Unit": "V", "@_Type": "AC_Voltage" },
{ "@_Value": "5.827", "@_Unit": "A", "@_Type": "AC_Current" },
{ "@_Value": "1363.2", "@_Unit": "W", "@_Type": "AC_Power" },
{ "@_Value": "1364.0", "@_Unit": "W", "@_Type": "AC_Power_fast" },
{ "@_Value": "49.986", "@_Unit": "Hz", "@_Type": "AC_Frequency" },
{ "@_Value": "576.8", "@_Unit": "V", "@_Type": "DC_Voltage" },
{ "@_Value": "2.501", "@_Unit": "A", "@_Type": "DC_Current" },
{ "@_Value": "577.9", "@_Unit": "V", "@_Type": "LINK_Voltage" },
{ "@_Value": "2849.6", "@_Unit": "W", "@_Type": "GridPower" },
{ "@_Value": "0.0", "@_Unit": "W", "@_Type": "GridConsumedPower" },
{ "@_Value": "2849.6", "@_Unit": "W", "@_Type": "GridInjectedPower" },
{ "@_Value": "0.0", "@_Unit": "W", "@_Type": "OwnConsumedPower" },
{ "@_Value": "70.0", "@_Unit": "%", "@_Type": "Derating" }
]
However, I would like to set the timestamp (root.Device.@_DateTime
) as a new key-pair value for each list entry.
Desired output:
[
{ "@_Value": "234.2", "@_Unit": "V", "@_Type": "AC_Voltage", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "5.827", "@_Unit": "A", "@_Type": "AC_Current", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "1363.2", "@_Unit": "W", "@_Type": "AC_Power", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "1364.0", "@_Unit": "W", "@_Type": "AC_Power_fast", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "49.986", "@_Unit": "Hz", "@_Type": "AC_Frequency", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "576.8", "@_Unit": "V", "@_Type": "DC_Voltage", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "2.501", "@_Unit": "A", "@_Type": "DC_Current", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "577.9", "@_Unit": "V", "@_Type": "LINK_Voltage", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "2849.6", "@_Unit": "W", "@_Type": "GridPower", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "0.0", "@_Unit": "W", "@_Type": "GridConsumedPower", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "2849.6", "@_Unit": "W", "@_Type": "GridInjectedPower", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "0.0", "@_Unit": "W", "@_Type": "OwnConsumedPower", "timestamp": "2024-06-26T10:07:27" },
{ "@_Value": "70.0", "@_Unit": "%", "@_Type": "Derating", "timestamp": "2024-06-26T10:07:27" }
]
I was wondering if it would be possible to create a new function for extend
to set static values to a column.
Thanks for your help!
Looks wonderful except when you use certain specific date ranges such 2012-12-14 00:00:00, the result set is as follows
{ "Name": "Zoro", "Place": "New York", "Longitude": 40.6943, "Latitude": -73.9249 }
no longer a table. interesting.
Originally posted by @yosiasz in grafana/grafana-infinity-datasource#315 (reply in thread)
Given some JSON like this:
[
{
"labels": {
"foo.bar.baz/qux": 42
}
}
]
I expect to be able to grab the innermost field like this (or with some other syntax):
parse-json
| extend "quux"="labels.foo.bar.baz/qux"
But it's silently missed.
Here it is in the Infinity datasource:
Looking at get_single_value
(
Line 5 in 3882e4d
get
, which can work around the issue by taking an array-type argument:
> _.get({"l": {"a.b": 42}}, "l.a.b")
undefined
> _.get({"l": {"a.b": 42}}, ["l", "a.b"])
42
I wonder if the fix is as simple as changing get_single_value
's query
type from string
to any
?
UQL datasources appear to be not supported on public dashboards.
View works fine within Grafana, when board is set as public, but viewing the board from a sharable url in browser results in UQL datasources not displaying data.
A fresh build fails on tests index.test.ts
and parser.extend.test.ts
I will raise a PR addressing these
index.tests.ts
FAIL src/parser/index.test.ts (145.972 s)
● uql › default
expect(received).toStrictEqual(expected) // deep equality
- Expected - 9
+ Received + 9
Array [
Object {
"disk": "DISK-F1266E1D0AAC2C3F",
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.disk.avail",
- "timestamp": 2069-11-11T09:38:20.000Z,
+ "timestamp": 2069-11-11T22:38:20.000Z,
"value": 11.1,
},
Object {
"disk": "DISK-F1266E1D0AAC2C3D",
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.disk.avail",
- "timestamp": 2069-11-11T09:38:20.000Z,
+ "timestamp": 2069-11-11T22:38:20.000Z,
"value": 111.1,
},
Object {
"disk": undefined,
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.cpu.idle",
- "timestamp": 2069-11-11T09:38:20.000Z,
+ "timestamp": 2069-11-11T22:38:20.000Z,
"value": 1.1,
},
Object {
"disk": "DISK-F1266E1D0AAC2C3F",
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.disk.avail",
- "timestamp": 2069-11-11T10:38:20.000Z,
+ "timestamp": 2069-11-11T23:38:20.000Z,
"value": 22.2,
},
Object {
"disk": "DISK-F1266E1D0AAC2C3D",
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.disk.avail",
- "timestamp": 2069-11-11T10:38:20.000Z,
+ "timestamp": 2069-11-11T23:38:20.000Z,
"value": 222.2,
},
Object {
"disk": undefined,
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.cpu.idle",
- "timestamp": 2069-11-11T10:38:20.000Z,
+ "timestamp": 2069-11-11T23:38:20.000Z,
"value": 2.2,
},
Object {
"disk": "DISK-F1266E1D0AAC2C3F",
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.disk.avail",
- "timestamp": 2069-11-11T11:38:20.000Z,
+ "timestamp": 2069-11-12T00:38:20.000Z,
"value": 33.3,
},
Object {
"disk": "DISK-F1266E1D0AAC2C3D",
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.disk.avail",
- "timestamp": 2069-11-11T11:38:20.000Z,
+ "timestamp": 2069-11-12T00:38:20.000Z,
"value": 333.3,
},
Object {
"disk": undefined,
"host": "HOST-F1266E1D0AAC2C3C",
"metricId": "builtin:host.cpu.idle",
- "timestamp": 2069-11-11T11:38:20.000Z,
+ "timestamp": 2069-11-12T00:38:20.000Z,
"value": 3.3,
},
]
85 | { data }
86 | )
> 87 | ).toStrictEqual([
| ^
88 | { timestamp: new Date("2069-11-11 22:38:20"), disk: "DISK-F1266E1D0AAC2C3F", host: "HOST-F1266E1D0AAC2C3C", value: 11.1, metricId: "builtin:host.disk.avail" },
89 | { timestamp: new Date("2069-11-11 22:38:20"), disk: "DISK-F1266E1D0AAC2C3D", host: "HOST-F1266E1D0AAC2C3C", value: 111.1, metricId: "builtin:host.disk.avail" },
90 | { timestamp: new Date("2069-11-11 22:38:20"), disk: undefined, host: "HOST-F1266E1D0AAC2C3C", value: 1.1, metricId: "builtin:host.cpu.idle" },
at src/parser/index.test.ts:87:7
at step (src/parser/index.test.ts:33:23)
at Object.next (src/parser/index.test.ts:14:53)
at fulfilled (src/parser/index.test.ts:5:58)
parser.extend.test.ts
FAIL src/parser/tests/parser.extend.test.ts (14.303 s)
● extend › date › add_datetime
expect(received).toStrictEqual(expected) // deep equality
- Expected - 1
+ Received + 1
Array [
Object {
- "in": 1990-02-26T10:00:00.000Z,
+ "in": 1990-02-26T23:00:00.000Z,
},
]
58 | expect(await uql(`extend "in"=todatetime("in") | extend "in"=add_datetime("in",'1d') | project "in"`, { data: [{ in: "1990-02-27" }] })).toStrictEqual([{ in: new Date("1990-02-28") }]);
59 | expect(await uql(`extend "in"=todatetime("in") | extend "in"=add_datetime("in",'-1d') | project "in"`, { data: [{ in: "1990-02-27" }] })).toStrictEqual([{ in: new Date("1990-02-26") }]);
> 60 | expect(await uql(`extend "in"=todatetime("in") | extend "in"=add_datetime("in",'-1h') | project "in"`, { data: [{ in: "1990-02-27" }] })).toStrictEqual([
| ^
61 | { in: new Date("1990-02-26 23:00:00") },
62 | ]);
63 | expect(await uql(`extend "in"=todatetime("in") | extend "in"=add_datetime("in",'-1y') | project "in"`, { data: [{ in: "1990-02-27" }] })).toStrictEqual([
at src/parser/tests/parser.extend.test.ts:60:146
at step (src/parser/tests/parser.extend.test.ts:33:23)
at Object.next (src/parser/tests/parser.extend.test.ts:14:53)
at fulfilled (src/parser/tests/parser.extend.test.ts:5:58)
# in a Ubuntu 20 WSL2 instance in timezone GMT+12
git clone https://github.com/yesoreyeram/uql.git
cd uql
# Check commit
git log -1
# commit 3882e4d3146b864cca9e5ac7fac5d718dcf9f964 (HEAD -> main, origin/main, origin/HEAD)
# Author: Sriramajeyam Sugumaran <[email protected]>
# Date: Mon Mar 27 07:39:30 2023 +0100
# Check Versions
node --version # responds with v18.17.1
npm --version # responds with 9.8.1
# Install and build
npm install
npm run build
The current UQL format_datetime() function needs to have some additional plugins to be able to use the week of year function and others. All the standard formats work fine. The Advanced format and plugins are shown below.
If possible could you provide some simple examples and responses for parse_url() and parse_urlquery() the latter currently has no documentation
Hi,
This is more a clarification than bug which could end up saving many frustration using jsonata in UQL.
This arose whilst trying to assist someone and where the jsonata query in using the JSONATA Exercise and that in UQL produced conflicting results.
Below is the json input, jsonata filer and result using the JSONATA Exerciser
When one tries same within UQL one does not get any response, which suggests a syntax issue.
After a few frustrating hours the problem was found to lie in the difference in use of " , ' and `.
In the JNOSATA Exerciser using " or ' for enclosing both defining new variable names works fine but within UQL " is a no-no and one must use '.
As the datasource has such wide functionality this can cause frustration over very simple issues, like I experienced. Jsonata allows very simple json re formatting and simple thing slike the difference between $ , $. , $$ , ` , ' , " can make all the difference as seen below where the revised query using ' also works in the JSONATA Exerciser but not with UQL.
Also note that the query
$.{'timestamp' : $fromMillis($toMillis(key_as_string)), 'DocCount': doc_count , 'Value' : average_of_age.value}
causes the nested json value column in GRAFANA to be A, whereas using 'Values' yields Values and 'VAL' yields VAL
I am not sure why the JSONATA Exerciser accepts ' and " but UQL Jsonata does not.
This shows one must take care as subtle difference can cause pain!!
Hope this helps
Say that my data looks like:
[
{
"title": "Pull Request Title 1",
"repository": {
"nameWithOwner": "org/repo"
},
"author": {
"login": "isaacsanders"
}
},
{
"title": "Pull Request Title 2",
"repository": {
"nameWithOwner": "org/repo"
},
"author": {
"login": "yesoreyeram"
}
}
]
How would one generate a table in Grafana that looks the table below?
Title | Author | Repository |
---|---|---|
Pull Request Title 1 | isanders | org/repo |
Pull Request Title 2 | yesoreyeram | org/repo |
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.