Status: 500. Message: db query error: pq: invalid input syntax for type json #4445

dayal18 · 2024-12-18T22:14:50Z

Is there an existing issue for this?

I have searched the existing issues

What happened?

Followed the exact steps mentioned here:

https://docs.teslamate.org/docs/installation/docker

Description: Ubuntu 24.04.1 LTS
Release: 24.04
Codename: noble

Docker version 27.3.1, build ce12230

teslamate:
image: teslamate/teslamate:latest
database:
image: postgres:17
grafana:
image: teslamate/grafana:latest
mosquitto:
image: eclipse-mosquitto:2

Expected Behavior

The dashboards are supposed to show data

Steps To Reproduce

Creating docker-compose.yml file as mentioned in this document (https://docs.teslamate.org/docs/installation/docker)
docker compose up
access the webUI using http://ipaddress:4000 (provide the access and refresh tokens)
access the Dashboard UI http://ipaddress:3000 (change admin password, and access battery dashboard)

Relevant log output

database-1   | 2024-12-18 22:09:35.034 UTC [89] ERROR:  invalid input syntax for type json at character 41
database-1   | 2024-12-18 22:09:35.034 UTC [89] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.034 UTC [89] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.034 UTC [89] STATEMENT:  SELECT
database-1   |    CASE WHEN 0 > 0 THEN 0 ELSE (''::json ->> 'MaxRange')::float END as "maxrange_mi",
database-1   |    (''::json ->> 'CurrentRange')::float  as "currentrange_mi",
database-1   |    CASE WHEN 0 > 0 THEN 0 ELSE (''::json ->> 'MaxRange')::float END - (''::json ->> 'CurrentRange')::float as "range_lost_mi"
grafana-1    | logger=context userId=1 orgId=1 uname=admin t=2024-12-18T22:09:35.03529099Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=192.168.86.130 time_ms=6 duration=6.782616ms size=577 referer="http://192.168.86.115:3000/d/jchmRiqUfXgTM/battery-health?orgId=1" handler=/api/ds/query status_source=downstream
database-1   | 2024-12-18 22:09:35.041 UTC [90] ERROR:  invalid input syntax for type json at character 30
database-1   | 2024-12-18 22:09:35.041 UTC [90] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.041 UTC [90] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.041 UTC [90] STATEMENT:  SELECT GREATEST(0, 100.0 - ((''::json ->> 'CurrentCapacity')::float * 100.0 / CASE WHEN 0 > 0 THEN 0 ELSE (''::json ->> 'MaxCapacity')::float END))
database-1   |
database-1   |
grafana-1    | logger=context userId=1 orgId=1 uname=admin t=2024-12-18T22:09:35.041586079Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=192.168.86.130 time_ms=5 duration=5.134265ms size=414 referer="http://192.168.86.115:3000/d/jchmRiqUfXgTM/battery-health?orgId=1" handler=/api/ds/query status_source=downstream
database-1   | 2024-12-18 22:09:35.076 UTC [90] ERROR:  invalid input syntax for type json at character 52
database-1   | 2024-12-18 22:09:35.076 UTC [90] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.076 UTC [90] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.076 UTC [90] STATEMENT:  SELECT
database-1   |    LEAST(100, (100 - GREATEST(0, 100.0 - ((''::json ->> 'CurrentCapacity')::float * 100.0 / CASE WHEN 0 > 0 THEN 0 ELSE (''::json ->> 'MaxCapacity')::float END)))) as "Battery Health (%)"
database-1   |
database-1   |
grafana-1    | logger=context userId=1 orgId=1 uname=admin t=2024-12-18T22:09:35.077350684Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=192.168.86.130 time_ms=5 duration=5.366221ms size=467 referer="http://192.168.86.115:3000/d/jchmRiqUfXgTM/battery-health?orgId=1" handler=/api/ds/query status_source=downstream
database-1   | 2024-12-18 22:09:35.080 UTC [91] ERROR:  invalid input syntax for type json at character 71
database-1   | 2024-12-18 22:09:35.080 UTC [91] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.080 UTC [91] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.080 UTC [91] STATEMENT:  SELECT
database-1   |          floor(sum(charge_energy_added) / CASE WHEN 0 > 0 THEN 0 ELSE (''::json ->> 'MaxCapacity')::float END) AS "# of Charging cycles"
database-1   |  FROM charging_processes WHERE car_id = 1 AND charge_energy_added > 0.01
grafana-1    | logger=context userId=1 orgId=1 uname=admin t=2024-12-18T22:09:35.081911145Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=192.168.86.130 time_ms=7 duration=7.241703ms size=2270 referer="http://192.168.86.115:3000/d/jchmRiqUfXgTM/battery-health?orgId=1" handler=/api/ds/query status_source=downstream
database-1   | 2024-12-18 22:09:35.108 UTC [87] ERROR:  invalid input syntax for type json at character 9
database-1   | 2024-12-18 22:09:35.108 UTC [87] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.108 UTC [87] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.108 UTC [87] STATEMENT:  SELECT (''::json ->> 'RatedEfficiency')::float * 10 / convert_km(1, 'mi') AS efficiency_mi
database-1   | 2024-12-18 22:09:35.109 UTC [88] ERROR:  invalid input syntax for type json at character 48
database-1   | 2024-12-18 22:09:35.109 UTC [88] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.109 UTC [88] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.109 UTC [88] STATEMENT:  SELECT * FROM ((SELECT usable_battery_level * (''::json ->> 'CurrentCapacity')::float / 100 as kWh, date, (''::json ->> 'CurrentCapacity')::float as Total
database-1   |  FROM positions
database-1   |  WHERE car_id = 1  AND usable_battery_level IS NOT NULL
database-1   |  ORDER BY date DESC
database-1   |  LIMIT 1)
database-1   |  UNION
database-1   |  (SELECT battery_level * (''::json ->> 'CurrentCapacity')::float / 100 as kWh, date, (''::json ->> 'CurrentCapacity')::float as Total
database-1   |  FROM charges c
database-1   |  JOIN charging_processes p ON p.id = c.charging_process_id
grafana-1    | logger=context userId=1 orgId=1 uname=admin t=2024-12-18T22:09:35.109453618Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=192.168.86.130 time_ms=6 duration=6.222082ms size=348 referer="http://192.168.86.115:3000/d/jchmRiqUfXgTM/battery-health?orgId=1" handler=/api/ds/query status_source=downstream
database-1   |  WHERE p.car_id = 1  AND usable_battery_level IS NOT NULL
grafana-1    | logger=context userId=1 orgId=1 uname=admin t=2024-12-18T22:09:35.109636506Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=192.168.86.130 time_ms=6 duration=6.425238ms size=876 referer="http://192.168.86.115:3000/d/jchmRiqUfXgTM/battery-health?orgId=1" handler=/api/ds/query status_source=downstream
database-1   |  ORDER BY date DESC
database-1   |  LIMIT 1)) AS last_usable_battery_level LIMIT 1
database-1   | 2024-12-18 22:09:35.142 UTC [87] ERROR:  invalid input syntax for type json at character 100
database-1   | 2024-12-18 22:09:35.142 UTC [87] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.142 UTC [87] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.142 UTC [87] STATEMENT:  SELECT convert_km(AVG(p.odometer)::numeric,'mi') AS "Odometer",
database-1   |          AVG(c.rated_battery_range_km * (''::json ->> 'RatedEfficiency')::float / c.usable_battery_level) AS "kWh",
database-1   |          --MAX(cp.id) AS id,
database-1   |          to_char(cp.end_date, 'YYYY-MM-dd') AS "Date"
database-1   |          FROM charging_processes cp
database-1   |                  JOIN (SELECT charging_process_id, MAX(date) as date     FROM charges WHERE usable_battery_level > 0 GROUP BY charging_process_id) AS last_charges       ON cp.id = last_charges.charging_process_id
database-1   |                  INNER JOIN charges c
database-1   |                  ON c.charging_process_id = cp.id AND c.date = last_charges.date
database-1   |                  INNER JOIN positions p ON p.id = cp.position_id
database-1   |          WHERE cp.car_id = 1
database-1   |                  AND cp.end_date IS NOT NULL
database-1   |                  AND cp.charge_energy_added >= (''::json ->> 'RatedEfficiency')::float
database-1   |          GROUP BY 3
database-1   | 2024-12-18 22:09:35.142 UTC [88] ERROR:  invalid input syntax for type json at character 155
database-1   | 2024-12-18 22:09:35.142 UTC [88] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:09:35.142 UTC [88] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:09:35.142 UTC [88] STATEMENT:  SELECT
database-1   |    ROUND(MIN(convert_km(p.odometer::numeric,'mi')),0) AS "Odometer",
database-1   |          ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY c.rated_battery_range_km * (''::json ->> 'RatedEfficiency')::float / c.usable_battery_level)::numeric,1) AS "kWh",
database-1   |          to_char(cp.end_date, 'YYYYMM') || CASE WHEN to_char(cp.end_date, 'DD')::int <= 15 THEN '1' ELSE '2' END  AS Title
database-1   |          FROM charging_processes cp
database-1   |                  JOIN (SELECT charging_process_id, MAX(date) as date     FROM charges WHERE usable_battery_level > 0 GROUP BY charging_process_id) AS last_charges       ON cp.id = last_charges.charging_process_id
database-1   |                  INNER JOIN charges c
database-1   |                  ON c.charging_process_id = cp.id AND c.date = last_charges.date
database-1   |                  INNER JOIN positions p ON p.id = cp.position_id
grafana-1    | logger=context userId=1 orgId=1 uname=admin t=2024-12-18T22:09:35.143609772Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=192.168.86.130 time_ms=6 duration=6.415031ms size=2236 referer="http://192.168.86.115:3000/d/jchmRiqUfXgTM/battery-health?orgId=1" handler=/api/ds/query status_source=downstream
database-1   |          WHERE cp.car_id = 1
database-1   |                  AND cp.end_date IS NOT NULL
database-1   |                  AND cp.charge_energy_added >= (''::json ->> 'RatedEfficiency')::float
database-1   |          GROUP BY 3
database-1   | 2024-12-18 22:10:49.309 UTC [88] ERROR:  invalid input syntax for type json at character 9
database-1   | 2024-12-18 22:10:49.309 UTC [88] DETAIL:  The input string ended unexpectedly.
database-1   | 2024-12-18 22:10:49.309 UTC [88] CONTEXT:  JSON data, line 1:
database-1   | 2024-12-18 22:10:49.309 UTC [88] STATEMENT:  SELECT (''::json ->> 'RatedEfficiency')::float * 10 / convert_km(1, 'mi') AS efficiency_mi

Screenshots

Additional data

No response

Type of installation

Docker

Version

Latest (v1.32)

dayal18 · 2024-12-19T12:29:17Z

All it needed was more data, a couple of charging sessions helped populate the data slowly. However, if anyone can include error handling when the data is NULL that would be something good to have.

swiffer · 2024-12-19T17:00:11Z

@dayal18 - i guess the problem no longer exists for you but opened a PR that improves the behavior.

luchtbakker · 2024-12-30T15:14:36Z

I have the same errors on the Battery Health page. I have now Teslamate with Grafana for 1 week on my synology nas, running in docker (Portainer).

How can i fix this?

swiffer · 2024-12-30T15:16:19Z

Wait for the next release or charge your car ;)

luchtbakker · 2024-12-30T15:18:06Z

Wait for the next release or charge your car ;)

As you can see, i've already charged my car 4 times in this current installation?

swiffer · 2024-12-30T15:33:29Z

Oh, is it an lfp battery ? What percentage have you charged to?

luchtbakker · 2024-12-30T15:43:56Z

No, i have the NMC-battery (Model Y 2024 Long Range RWD) . But i see that LFP battery is enabled in the Teslamate settings...

For now disabled. I hope the data will be recovered soon.

swiffer · 2024-12-31T09:10:21Z

🤔 i wonder why it happens in your case... can you go into dashboard settings -> Variable -> aux and see the preview of the value? is it completely empty? - what shows up if you change the sql to this one:

WITH Aux AS
(
		SELECT 
    car_id,
		COALESCE(efficiency, 
		(SELECT efficiency
			FROM cars WHERE id = $car_id) * 100) AS efficiency
	FROM (
		SELECT ROUND((charge_energy_added / NULLIF(end_rated_range_km - start_rated_range_km, 0))::numeric, 3) * 100 as efficiency,
			COUNT(*) as count, $car_id AS car_id 
		FROM charging_processes
		WHERE car_id = $car_id
			AND duration_min > 10
			AND end_battery_level <= 95
			AND start_rated_range_km IS NOT NULL
			AND end_rated_range_km IS NOT NULL
			AND charge_energy_added > 0
		GROUP BY 1
		ORDER BY 2 DESC
		LIMIT 1
	) AS DerivatedEfficiency
),
CurrentCapacity	 AS
(
	SELECT AVG(Capacity) AS Capacity
FROM (
SELECT 
	c.rated_battery_range_km * aux.efficiency / c.usable_battery_level AS Capacity
	FROM charging_processes cp
		INNER JOIN charges c
		ON c.charging_process_id = cp.id 
                INNER JOIN aux ON cp.car_id = aux.car_id
	WHERE cp.car_id = $car_id
		AND cp.end_date IS NOT NULL
		AND cp.charge_energy_added >= aux.efficiency
		AND c.usable_battery_level > 0
	 ORDER BY cp.end_date DESC LIMIT 10) AS lastCharges
), 
MaxCapacity AS
(
	SELECT 
		MAX(c.rated_battery_range_km * aux.efficiency / c.usable_battery_level) AS Capacity
	FROM charging_processes cp
		INNER JOIN (
			SELECT charging_process_id, MAX(date) as date FROM charges WHERE usable_battery_level > 0 GROUP BY charging_process_id) AS gcharges	
			ON cp.id = gcharges.charging_process_id
		INNER JOIN charges c
		ON c.charging_process_id = cp.id AND c.date = gcharges.date
		INNER JOIN aux ON cp.car_id = aux.car_id
	WHERE cp.car_id = $car_id
		AND cp.end_date IS NOT NULL
		AND cp.charge_energy_added >= aux.efficiency
), 
CurrentRange AS
(
    SELECT (range * 100.0 / usable_battery_level) AS range
	FROM (
		(SELECT date, ${preferred_range}_battery_range_km AS range, usable_battery_level AS usable_battery_level
			FROM positions	WHERE car_id = $car_id AND ideal_battery_range_km IS NOT NULL AND usable_battery_level > 0  ORDER BY date DESC LIMIT 1)
		UNION ALL
		(SELECT date, ${preferred_range}_battery_range_km AS range, usable_battery_level as usable_battery_level
			FROM charges c JOIN charging_processes p ON p.id = c.charging_process_id
			WHERE p.car_id = $car_id AND usable_battery_level > 0 ORDER BY date DESC LIMIT 1)
	) AS data
	ORDER BY date DESC
	LIMIT 1
), 
MaxRange AS
(
    SELECT
		floor(extract(epoch from date)/86400)*86400 AS time,
	    CASE WHEN sum(usable_battery_level) = 0 THEN sum(${preferred_range}_battery_range_km) * 100
		     ELSE sum(${preferred_range}_battery_range_km) / sum(usable_battery_level) * 100
	END AS range
    FROM (
		SELECT battery_level, usable_battery_level, date,  ${preferred_range}_battery_range_km from charges c 
		JOIN charging_processes p ON p.id = c.charging_process_id 
		WHERE p.car_id = $car_id AND usable_battery_level IS NOT NULL) AS data
	GROUP BY 1
	ORDER BY 2 DESC
	LIMIT 1
),
Base as (
	select null
)
SELECT
  json_build_object(
    'MaxRange', convert_km(MaxRange.range,'$length_unit'),
    'CurrentRange', convert_km(CurrentRange.range,'$length_unit'),
    'MaxCapacity', MaxCapacity.Capacity,
    'CurrentCapacity', CASE WHEN CurrentCapacity.Capacity IS NULL THEN 1 ELSE CurrentCapacity.Capacity END,
    'RatedEfficiency', aux.efficiency
  )
FROM Base, MaxRange, CurrentRange, Aux, MaxCapacity, CurrentCapacity

luchtbakker · 2024-12-31T14:01:47Z

Sure :)

But currently see: There are no variables added.
When i click on add variables i see this.

What should I select and where should I add your code?

swiffer · 2024-12-31T14:10:00Z

You need to go to the battery health dashboard first

luchtbakker · 2025-01-01T09:15:06Z

You need to go to the battery health dashboard first

Okay, en than new variables. What i must be select under: Select variable type?
Sorry, I'm a little less familiar with this, despite being an IT guy... Haha

swiffer · 2025-01-01T12:01:20Z

just open the variable labeled "aux" and check it's output (bottom -> Preview) and post it here.

Afterwards you can modify the query and recheck the output.

luchtbakker · 2025-01-01T12:14:55Z

just open the variable labeled "aux" and check it's output (bottom -> Preview) and post it here.

Afterwards you can modify the query and recheck the output.

Ah, thanks. The current preview value is: None
After i changed the Query with your code, Apply and Run Query, nothing change.

swiffer · 2025-01-01T12:28:01Z

🤔what version of postgres are you using?

luchtbakker · 2025-01-01T12:36:15Z

🤔what version of postgres are you using?

Postgres 14.

swiffer · 2025-01-01T12:48:51Z

14 should be fine (while we recommend the latest (17)) - Don't understand why at least the updated query is not returning a valid json string.

luchtbakker · 2025-01-01T12:52:20Z

I don't know :-( . I can give you remote access to the environment if you want? I just don't know how to share the login details with you.

swiffer · 2025-01-01T12:54:37Z

I wouldn't have asked but do appreciate the trust. You could send over the secrets via saltify by making use of my commit email.

https://www.saltify.io/

luchtbakker · 2025-01-01T12:57:45Z

Don't feel obligated. I'm already glad you're so eager to help! :)

I'll send it to hello att saltify.io . Give me 15 minutes :)

swiffer · 2025-01-01T12:58:57Z

no, it's just the service you can use. sorry causing any misunderstanding.

you can find the email in the second line here: https://github.com/teslamate-org/teslamate/commit/c7770ec12199afc1239266d968f25ca22cc432c9.patch

luchtbakker · 2025-01-01T13:06:56Z

Thanks, it's send! :)

swiffer · 2025-01-01T13:46:11Z

Ok, found it. Learned that #4448 is not fully fixing the issue as CROSS JOIN cannot be used if one of the tables is empty. Opened #4463 and patched your local dashboard ;)

To reliably calculate Efficiency you have to charge for min 10 minutes and max 95%. Afterwards other dashboards will also fully populate (e.g. Efficiency Dashboard).

I will look into ways to further improve the Query tomorrow but glad to have found an explanation.

luchtbakker · 2025-01-01T13:49:49Z

Glad you were able to find it, and that I could make a small contribution to it! :)

I will keep an eye on the dashboard in the coming days to see if everything works well.

Thanks again for your help! :)

dayal18 closed this as completed Dec 19, 2024

swiffer added a commit to swiffer/teslamate that referenced this issue Dec 19, 2024

fix for teslamate-org#4445

205f955

swiffer mentioned this issue Dec 19, 2024

fix for battery health dashboard erroring out if no charge data has been collected so far #4448

Merged

JakobLichterfeld added enhancement New feature or request area:dashboard Related to a Grafana dashboard labels Dec 21, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Status: 500. Message: db query error: pq: invalid input syntax for type json #4445

Status: 500. Message: db query error: pq: invalid input syntax for type json #4445

dayal18 commented Dec 18, 2024

dayal18 commented Dec 19, 2024

swiffer commented Dec 19, 2024

luchtbakker commented Dec 30, 2024

swiffer commented Dec 30, 2024

luchtbakker commented Dec 30, 2024

swiffer commented Dec 30, 2024

luchtbakker commented Dec 30, 2024

swiffer commented Dec 31, 2024

luchtbakker commented Dec 31, 2024 •

edited

Loading

swiffer commented Dec 31, 2024

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025 •

edited

Loading

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

Status: 500. Message: db query error: pq: invalid input syntax for type json #4445

Status: 500. Message: db query error: pq: invalid input syntax for type json #4445

Comments

dayal18 commented Dec 18, 2024

Is there an existing issue for this?

What happened?

Expected Behavior

Steps To Reproduce

Relevant log output

Screenshots

Additional data

Type of installation

Version

dayal18 commented Dec 19, 2024

swiffer commented Dec 19, 2024

luchtbakker commented Dec 30, 2024

swiffer commented Dec 30, 2024

luchtbakker commented Dec 30, 2024

swiffer commented Dec 30, 2024

luchtbakker commented Dec 30, 2024

swiffer commented Dec 31, 2024

luchtbakker commented Dec 31, 2024 • edited Loading

swiffer commented Dec 31, 2024

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025 • edited Loading

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

swiffer commented Jan 1, 2025

luchtbakker commented Jan 1, 2025

luchtbakker commented Dec 31, 2024 •

edited

Loading

swiffer commented Jan 1, 2025 •

edited

Loading