Accessing multiple fields in InfluxDB 2.0 records

Question

im new to InfluxDB 2.0 and am building a time series database where I store multiple fields per Point (price values for XAUUSD currency).

While I am able to store it as expected; when I fetch the records, I cant seem to get all the fields accessible per record.

Here is a code snippet of how I write some dummy data to DB:

from datetime import datetime
import time
import influxdb_client
from influxdb_client.client.write_api import SYNCHRONOUS
import random

token = "XXX"
org = "Trader"
bucket = "Master"
url="http://localhost:8086"

client = influxdb_client.InfluxDBClient(
    url=url,
    token=token,
    org=org
)

write_api = client.write_api(write_options=SYNCHRONOUS)
while True:
    p = influxdb_client.Point("D1").tag("currency", "XAUUSD").field("open", random.randint(900,1100)).field("close", random.randint(900,1100)).time(datetime.utcnow(), influxdb_client.WritePrecision.NS)
    write_api.write(bucket=bucket, org=org, record=p)
    time.sleep(1)

and I am trying to read the data back as:

query_api = client.query_api()
query = ' from(bucket:"Master")\
|> range(start: -5h)\
|> filter(fn:(r) => r._measurement == "D1")\
|> filter(fn: (r) => r.currency == "XAUUSD")\
|> filter(fn:(r) => r["_field"] == "close" or r["_field"] == "open")'

result = client.query_api().query(org=org, query=query)

for table in result:
  for record in table.records:
    results.append((record.get_field(), record.get_value()))

print(results)

The problem is; each row result is like:

{'result': '_result', 'table': 1, '_start': datetime.datetime(2021, 5, 4, 8, 58, 35, 12587, tzinfo=tzutc()), '_stop': datetime.datetime(2021, 5, 4, 13, 58, 35, 12587, tzinfo=tzutc()), '_time': datetime.datetime(2021, 5, 4, 13, 12, 56, 86095, tzinfo=tzutc()), '_value': 961, '_field': 'open', '_measurement': 'D1', 'currency': 'XAUUSD'}

and its not showing both the fields; open and close (they appear as separate rows where _field is "open" for one entry and "close" for the second entry" for the same entry.

Is there a way I can get the result row to have both the field values in one result rather than 2; 1 for each field? Because if I add more fields, I'll have to find a way to combine n rows for the same price tick.

I have tried to go through the InfluxDB documentation but all the examples show only one _field value and not multiple.

There are some answers online that use pivot with regex but I dont think that suits my case where a simple query such as this in MySQL would be:

SELECT open, close FROM XAUUSD WHERE interval="D1";

Any idea or help on how I can solve this "simple" task with InfluxDB or am I just using the wrong tool for this job?

Carbon_Unit Carbon_Unit · Accepted Answer · 2021-07-19T21:57:34

I had the same question.
This link helped me.

Pivot fields into columns
Use pivot() to pivot the mem_used and mem_total fields into columns. Output includes mem_used and mem_total columns with values for each corresponding _time.

query = ' from(bucket:"Master")\
|> range(start: -5h)\
|> filter(fn:(r) => r._measurement == "D1")\
|> filter(fn: (r) => r.currency == "XAUUSD")\
|> filter(fn:(r) => r["_field"] == "close" or r["_field"] == "open")\
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")'  # <- Add this

Accessing multiple fields in InfluxDB 2.0 records

1 Answers