# From sqlite to json format

**URL:** <https://discourse.nodered.org/t/from-sqlite-to-json-format/22227>\
**Category:** General\
**Created:** [25 February 2020 04:40 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227 "2020-02-25T04:40:59Z")\
**Posts on this page:** 7\
**Page:** 2

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [4 March 2020 12:54 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227/21 "2020-03-04T12:54:51Z")

</div>

Ok, there are a nuber of problems in that your data is not in the correct format for the chart.

issue 1: You have a number of errors in the jsonata in the change node. Use this in place of what you have:

```auto
[
   {
       "series": ["HVACHP"],
       "data": [[payload[[0..99]]]],
       "labels": [[""]]

   }

]

```

issue 2: the format of the data sent into the chart node must use 'x' and 'y' so you have to change the SQL query to return 'x' inplace of 'UTC' and 'y' inplace of 'hp' - change the query in the function node to:

```auto
var b= " SELECT UTC as 'x', hp as 'y' FROM hvac4 WHERE UTC BETWEEN "+ stday2 +" AND "+ enday2+" " 

```

issue 3: change the flow so the `change` node is connected to the `sql` node and the `change` nodt feeds the `chart` node directly.

See how that works.

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [4 March 2020 16:52 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227/22 "2020-03-04T16:52:05Z")

</div>

@Colin

Hi Colin

I been searching a lot on internet to create this type of db.  
I saw many examples to format sqlite db in that way.  
What I'm trying to do is shown on chart some parameter over the time and  
the real time parameters are been watching on a dashboard which I've already working.

I'm not software engineer, I'm still learning about JS that the reason I choose Node-red.  
So if you have a better alternative to manage db for historical purpose is gonna be very much  
appreciated  
In the mean time I gonna go on with Paul who is helping me a lot.  
He really make me struggle with this stuff and I appreciate it because is the best way to learn .  
Kind Regards,  
Alex

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [4 March 2020 16:53 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227/23 "2020-03-04T16:53:26Z")

</div>

@zenofmud  
Thank you for your time.  
I will give try to your suggestion.  
Kind regard,  
Alex

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [4 March 2020 17:21 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227/24 "2020-03-04T17:21:35Z")

</div>

If you want to look at historical data, zoom in and out and so on then many suggest that using Influxdb with Grafana is a good way to go. With Grafana you will get much better graphical capabilities than you will get trying to do it yourself with the node red dashboard chart, which is designed as a quick and easy way to real time charts. There are influxdb nodes in node-red for easily writing the data to the database, then you use Grafana for viewing them.

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [5 March 2020 03:07 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227/25 "2020-03-05T03:07:11Z")

</div>

@zenofmud

Yes, Sir !

With your last suggestion the chart is now working !!!  
I'm gonna analyze the changes to comprehend them.  
I don't like using something without understanding it and repeating it as a parrot. 😄  
Than you very much for your time

---

<div class="post-metadata">

**Author:** ![NODE777](https://avatars.discourse-cdn.com/v4/letter/n/7993a0/32.png) [@NODE777](https://discourse.nodered.org/u/NODE777)\
**Post date:** [5 March 2020 03:15 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227/26 "2020-03-05T03:15:26Z")

</div>

@spyder0552

Hi  
Take a look at the last Paul's post.

I already have the chart working !!!

Thank you for your help, also !!!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [19 March 2020 03:15 UTC](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227/27 "2020-03-19T03:15:28Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

[Previous page](https://discourse.nodered.org/t/from-sqlite-to-json-format/22227.md?page=1)