[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

JSON

794

Solutions

802

Contributors

JavaScript Object Notation, or JSON, is human-readable text used when transmitting data objects consisting of attribute:value pairs between a server and a web application as an alternative to XML. JSON, while it is originally derived from JavaScript, is a language-independent data format. Code for parsing and generating JSON data is available in many programming languages.

Share tech news, updates, or what's on your mind.

Sign up to Post

For some reason, maybe due to schema or lack thereof, my JSON documents are flagged with "Unexpected character sequence in member name".

And also, the member names do not have quotes around them when I first open the document, but then either through process of saving or modifying the documents, the member names get surrounded with double-quotes.

 View: "vAnnouncements",

becomes:

"View": "vAnnouncements",

I could use some help with this!

I just recently ran "Get Latest" from the TFS, so the double-quotes problem corrected as a result.  But I am still getting the "Unexpected character" thing.
     

-Tom
0
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hello Expert,

Could anybody please through light on following query. How json table works here !

SELECT ext.legacy_id        legacy_id,
                               ext.record_type      record_type,
                               ext.tran_type        tran_type,
                               ext.corp_id          corp_id,
                               ext.mdse_co_id       mdse_co_id,
                               json.loc             location,
                               ext.sty_cd           sty_cd,
                               ext.sty_clr_cd       sty_clr_cd,
                               ext.mkt_cd           mkt_cd,
                               ext.unit_price       unit_price,
                               ext.effective_date   effective_date
                          FROM risprc.ris_legacy_price_stg ext,
                               json_table('['||ext.excep_str_nbr||']', '$[*]' COLUMNS loc  NUMBER PATH '$') json,   --How it works?
                               TABLE (CAST (L_loc_tbl AS NUMBER_TBL)) tbl
                         WHERE ext.record_type   = LP_corp_rec_type
                           AND ext.excep_str_nbr IS NOT NULL
                           AND ext.location      = VALUE(tbl) ;

Open in new window

0
I am trying to read in a JSON  stream and able to use it to look up the datatype of a property. please see below.
What do I need to call using JSON.net to structure a proper lookup of an array of elements,  Below is a summary what I would like to do:
1. Lookup to see if there is a match of one of the following keyword:- 'Device/AnalogInput/AnalogOutput ...'.  This list can vary at runtime hence I can not write a class for each keyword
2. if exist,  loop through the property to find out the data type of each property
For example
I check if there is a keyword called 'Device',  Once I find it exists, I would like to loop through to find the 'datatype' of each property. In this case, I would find out 'datatype' of property 'SystemStatus' is 'int'. Next, I would find out 'datatype' of property 'ObjectName' is 'string'.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;


namespace TestJSONLookup
{
    class Program
    {
        static void Main(string[] args)
        {
            string json = @"[
  {
    'Device': [
      {
        'property': 'SystemStatus',
        'dataType': 'int'
      },
      {
        'property': 'ObjectName',
        'dataType': 'string'
      }
    ]
  },
  {
    'AnalogInput': [
      {
        'property': 'PresentValue',
        'type': 'real'
      }
    ]
  },
  {
    'AnalogOutput' : [
      {
    

Open in new window

0
Migrating Oracle procedures which has JSON data as input and business validation to insert/ update/delete tables to MariaDB.

Can we use SQL_MODE to ORACLE and use the same procedure ? Or we need to rewrite it again.

Like few differences where we use JSON_PARSER.PARSER(<<my input JSON>>) or JSON_EXT.GET_NUMBER / JSON_EXT.GET_STRING in Oracle to get the values for specific keys. So can we directly use the same procedure to work on Mariadb by setting the mode or we need to rewrite the entire procedure.
0
Hi there, I am trying to connect to the TSheets(www.tsheets.com) API, and load all the job codes into a table . I can successfully connect and get the JSON, but I currently have to hard code the job code in order to get the info. I am stuck on how to get the codes via the keys/item in the dictionary.  In the example below, '5834988' is the job code, but I have to hard code it in. I just want to pull '5834988' via the dicationary.

Sample JSON:

{
 "results": {
  "jobcodes": {
   "5834988": {
    "id": 5834988,
    "parent_id": 0,
    "assigned_to_all": true,
    "billable": false,
    "active": true,
    "type": "regular",
    "has_children": false,
    "billable_rate": 0,
    "short_code": "",
    "name": "Job 1000"
}
}
}
}

Code:

Set p = JsonConverter.ParseJson(jsonStr)
   
    For Each key In p("results")("jobcodes").Keys
        Debug.Print p("results")("jobcodes")("5834988").item("id")
        Debug.Print p("results")("jobcodes")("5834988").item("parent_id")
        Debug.Print p("results")("jobcodes")("5834988").item("active")
        Debug.Print p("results")("jobcodes")("5834988").item("type")
        Debug.Print p("results")("jobcodes")("5834988").item("name")
 
    Next key
0
Hi Experts!

Could you suggest the beter way to convert this json structure to a PHP array?

Sample: 
{
  "numeroGuia": "sample string 1",
  "paciente": {
    "nome": "sample string 1",
    "endereco": {
      "cep": "sample string 1",
      "logradouro": "sample string 2",
      "numero": 3,
      "complemento": "sample string 4",
      "bairro": "sample string 5",
      "cidade": "sample string 6"
    },
    "dataNascimento": "2018-10-22T17:53:58.4603585-03:00",
    "cpf": "sample string 2",
    "telefone": "sample string 3",
    "email": "sample string 4",
    "sexo": "sample string 5",
    "peso": 1.1,
    "convenio": 1,
    "plano": "sample string 6",
    "matricula": "sample string 7"
  },
  "contratante": {
    "tipo": 64,
    "contrato": 1
  },
  "atendimento": {
    "diagnostico": "sample string 1",
    "recurso": "sample string 2",
    "exames": "sample string 3",
    "carater": 64,
    "motivoAtendimento": "sample string 4",
    "cid": "sample string 5",
    "produto": "sample string 6"
  },
  "captacao": {
    "cor": "sample string 1",
    "dataInicio": "2018-10-22T17:53:58.4759589-03:00",
    "dataFim": "2018-10-22T17:53:58.4759589-03:00"
  },
  "origem": {
    "dataAgendadaChegadaOrigem": "2018-10-22T17:53:58.4759589-03:00",
    "nomeLocal": "sample string 1",
    "endereco": {
      "cep": "sample string 1",
      "logradouro": "sample string 2",
      "numero": 3,
      "complemento": "sample string 4",
      "bairro": "sample string 5",
      

Open in new window

0
How do i work with this data ?

column
above is the column

below is the data structure in this column

data structure
i am formatting it as json in the column but how do i access is on php from i pull it from the data and select the values of ids i want ?
0
I'm trying to learn about web APIs and was wondering if you could help me with a simple one to retrieve information about an online image.  An example URL could be
https://www.google.co.uk/imgres?imgurl=https%3A%2F%2Fvignette.wikia.nocookie.net%2Fjurassicpark%2Fimages%2F7%2F74%2FApatosaurus.png%2Frevision%2Flatest%3Fcb%3D20150103191434&imgrefurl=http%3A%2F%2Fjurassicpark.wikia.com%2Fwiki%2FApatosaurus&docid=inqHuw-Ju5m40M&tbnid=rDDyWwT3vIXnGM%3A&vet=10ahUKEwiD34KBu5LeAhUkJMAKHb9gADEQMwhqKAIwAg..i&w=1288&h=540&bih=969&biw=1920&q=apatosaurus&ved=0ahUKEwiD34KBu5LeAhUkJMAKHb9gADEQMwhqKAIwAg&iact=mrc&uact=8

How could I build a RESTful API which accepts the image url as a parameter and retrieve information about the image must be retrieved responding in JSON format.  Is it also possible to create a web client to call the API.  It only needs a single page to accept input for the url, a button to call the API, and an area to display the response.

I'm using C# within Visual Studio 2017 community edition.
0
Hello -

I need help formatting a sharepoint column with JSON.  I am using O365 which allows custom formatting of columns using JSON.  I need to keep the column format as a number format (cannot use text format) and need to basically replace the thousands comma with a hyphen.  So, a number like 18,212 would show up at 18-212.

Again, I can't change the column format to be text.  I have very limited experience with JSON, so any help would be appreciated.
0
Hi, I have a python script that is creating a DataFrame from some json data.
I can create a DataFrame (df) from the data, but I need to create a DataFrame from the 'readings' column within the df DataFrame. My code is failing because the 'readings' column is a list.
Ultimately I need to create a DataFrame with the two DataFrames combined:

DataFrame needed
This is the python code I'm working with:

import pandas as pd
import json

data = {  
   'locationId':123546987,
   'parameters':[  
      {  
         'parameterId':'11',
         'unitId':'81',
         'customParameter':False,
         'readings':[  
            {  
               'timestamp':1538957700,
               'value':2306.078
            },
            {  
               'timestamp':1538959500,
               'value':2305.892
            },
            {  
               'timestamp':1538961300,
               'value':2305.981
            }
         ]
      },
      {  
         'parameterId':'1',
         'unitId':'1',
         'customParameter':False,
         'readings':[  
            {  
               'timestamp':1538957700,
               'value':25.575
            },
            {  
               'timestamp':1538959500,
               'value':25.572
            },
            {  
               'timestamp':1538961300,
               'value':25.575
            }
         ]
      }
   ]
}
         
val = json.loads(json.dumps(data))

val1 = val['parameters']

#val2 = [{'timestamp': 

Open in new window

0
Introduction to R
LVL 12
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

How do I extract parent values and all child values from deserialized JSON, one record per child value, to a List<T>?

Sample JSON:  json_sample.txt

So from the attached sample JSON we wish to add the following records to the  list:
Record 1:  aen: Y456789, studentID: 1016, need: MMM, rank: 1
Record 2:  aen: Y456789, studentID: 1016, need: PPP, rank: 2

All records with "needs : null" to be ignored and all "need": "Not Specified" to be ignored. Sample code below:



      CTFNeedDataBatch =
                           (from people in myJSON.content
                            select new NeedCTF
                            {
                                studID = people.StudentBasic.studentId.ToString(),
                                AEN= people.StudentBasic.aen.ToString(),
                                Code = (people.StudentSensitive.needs == null ? people.StudentSensitive.needs[0].need.ToString() : ""),
                                Priority = (people.StudentSensitive.needs == null ? people.StudentSensitive.needs[0].rank.ToString() : ""),
                            }).Where(w => w.Code != "").ToList();


The problem with this code is we are not getting any values from the "needs" element and even if we were, I doubt we would be getting more than one record from an element that has more than one need.
0
Hi,

Using Python I'm trying to read a json string and output it to a dataframe, but I get the following error:

TypeError: 'float' object is not subscriptable

code is here:
# We import the requests module which allows us to make the API call
import pandas as pd
import ast
import json
import requests

# Call API to pull data
url = 'https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=b6907d289e10d714a6e88b30761fae22'

response = requests.get(url = url)
response_data = response.json()

#data = response_data

data = """{  
   'coord':{  
      'lon':-0.13,
      'lat':51.51
   },
   'weather':[  
      {  
         'id':300,
         'main':'Drizzle',
         'description':'light intensity drizzle',
         'icon':'09d'
      }
   ],
   'base':'stations',
   'main':{  
      'temp':280.32,
      'pressure':1012,
      'humidity':81,
      'temp_min':279.15,
      'temp_max':281.15
   },
   'visibility':10000,
   'wind':{  
      'speed':4.1,
      'deg':80
   },
   'clouds':{  
      'all':90
   },
   'dt':1485789600,
   'sys':{  
      'type':1,
      'id':5091,
      'message':0.0103,
      'country':'GB',
      'sunrise':1485762037,
      'sunset':1485794875
   },
   'id':2643743,
   'name':'London',
   'cod':200
}"""

val = ast.literal_eval(data)
val1 = json.loads(json.dumps(val))
val2 = val1['main']['temp'][0]
dataset = pd.DataFrame(val2)
#OutputDataSet=dataset
#print(val1)
print(dataset)

Open in new window

0
How to order below json by Location , Name ="Total" comes before detail

{
   "records":[
      {
         "Location":"UK",
         "RecordType":"Fruits",
         "Qty":16,
         "Name":"Total"
      },
      {
         "Location":"UK",
         "RecordType":"Grocery",
         "Qty":11,
         "Name":"Total"
      },
      {
         "Location":"US",
         "RecordType":"Fruits",
         "Qty":5,
         "Name":"Total"
      },
        {
         "Location":"US",
         "RecordType":"Grocery",
         "Qty":17,
         "Name":"Total"
      },
      {
         "Location":"US",
         "RecordType":"Fruits",
         "Productid":123,
         "Qty":5,
         "Name":"Apple"
      },
      {
         "Location":"UK",
         "RecordType":"Fruits",
         "Productid":124,
         "Qty":11,
         "Name":"Bananna"
      },

      {
         "Location":"US",
         "RecordType":"Grocery",
         "Productid":223,
         "Qty":6,
         "Name":"Rice"
      },
      {
         "Location":"UK",
         "RecordType":"Grocery",
         "Productid":224,
         "Qty":11,
         "Name":"Beans"
      }
   ]
}

Open in new window




How to rearrange json so Location + Total comes before detail

{
   "records":[
      {
         "Location":"UK",
         "RecordType":"Fruits",
         "Qty":16,
         "Name":"Total"
      },
      {
         "Location":"UK",
         "RecordType":"Fruits",
         "Productid":124,
         "Qty":11,
         "Name":"Bananna"
      },
      {
         "Location":"UK",
         "RecordType":"Grocery",
         "Qty":11,
         "Name":"Total"
      },
      {
         "Location":"UK",
         "RecordType":"Grocery",
         "Productid":224,
         "Qty":11,
         "Name":"Beans"
      },
      {
         "Location":"US",
         "RecordType":"Fruits",
         "Qty":5,
         "Name":"Total"
      },
      {
         "Location":"US",
         "RecordType":"Fruits",
         "Productid":123,
         "Qty":5,
         "Name":"Apple"
      },
        {
         "Location":"US",
         "RecordType":"Grocery",
         "Qty":17,
         "Name":"Total"
      },
      {
         "Location":"US",
         "RecordType":"Grocery",
         "Productid":223,
         "Qty":6,
         "Name":"Rice"
      }
   ]
}

Open in new window

0
Hello Sirs
I am using serious issues with Charset Encoding in a REST application. I use Spring and the encoding in the bean MessageSource and Tymeleaf resolver  is set to UTF-8.
I get this when I return Json string:
\ufffd replacing special characters like é, or any other letter with accent.
How can I solve this?

Best regards.
0
Extracting API Data Using Python and Loading into SQL Server

Hi,
I am new to Python in SQL Server. I'd like to load json data from an API into SQL Server, I thought the best way to do this is to utilise the new SQL Server Machine Learning Services with Python.

I can call the API and print the json data in SSMS:

execute sp_execute_external_script 
@language = N'Python',
@script = N'

# We import the requests module which allows us to make the API call
import pandas as pd
import json
import requests
 
# Call API to pull data
url = ''https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=b6907d289e10d714a6e88b30761fae22''

response = requests.get(url = url)
response_data = response.json()

print(response_data)
'

Open in new window


I'm pretty happy using the JSON functions in SQL Server to format and parse the data into SQL tables, but with Python how do I read/access the json data from the response into an TSQL query?

Thank you
0
What is the syntax for a LINQ query that would get the first value from a JSON child node. I'm trying to get the first value in the "aenHistory" element in the attached JSON sample.

Here is the query that works fine so far apart from the "FormerAEN" element:

                                    studentDataBatch =
                                                  (from people in students.Children().Children()                                                    
                                                   select new studentSIM
                                                   {
                                                       PersonID = (people.SelectToken("StudentBasic.studentId") == null ? String.Empty : people.SelectToken("StudentBasic.studentId").Value<string>()),
                                                       Surname = people.SelectToken("StudentBasic.surname").Value<string>(),
                                                       Forename = people.SelectToken("StudentBasic.firstName").Value<string>(),
                                                       AEN = (people.SelectToken("StudentBasic.aen") == null ? String.Empty : people.SelectToken("StudentBasic.aen").Value<string>()),                                                      
                                                      Sex = (people.SelectToken("StudentBasic.gender").Value<string>()).Substring(0,1),
                                                     …
0
Hi,

I've a form to send a ajax request which return a json object from a cURL request. I use the form to query the API using the start and and the end date. I've managed to show the datas returned into a table as suggested in this post https://www.experts-exchange.com/questions/29120674/Convert-json-to-html-table.html
Now my problem is every time a make a new request from my form a new table is shown to the form page just after the table I've obtained with the previous request, is there any way to show only one table, so at every form request the previus data is replaced with the new data?

This is my javascript code:
    $(document).ready(function() {

      // Function to create table layout
      function createTable(data) {
        var table = document.createElement('table');
        var header = table.insertRow();

        for(var h in data[0]) {
          var th = document.createElement('th');
          th.innerHTML = h;
          header.appendChild(th);
        }

        table.classList.add('table','table-bordered');
        data.forEach(function(item) {
          var row = table.insertRow();
          for(var v in item) {
            var cell = row.insertCell();
            if (Array.isArray(item[v])) {
              var subtable = createTable(item[v]);
              cell.appendChild(subtable);
            }
            else {
              cell.innerHTML = item[v];
            }
          }
        })

        return table;
      }

      // 

Open in new window

0
How can I build html table from json? for negative value I want to bold the row. and display NJ warehouse first then CA
{
"action": "query",
"Time": 12,
"data": [
{
  "Product": "Iphone",
  "Price": 989,
  "Warehouse": "NJ"
},
{
  "Product": "Ipad mini",
  "Price": 429,
  "Warehouse": "NJ"
},
{
  "Product": "Iphone mini",
  "Price": 489,
  "Warehouse": "NJ"
},
{
  "Product": "Galaxy",
  "Price": -1,
  "Warehouse": "CA"
},
{
  "Product": "Galaxy mini",
  "Price": "595.99",
  "Warehouse": "CA"
}
]    
}

Open in new window


The result should be as below, first display all Warehouse=US then Warehouse=CA

US Product Price Warehouse Iphone 989.00 US Ipad mini 429.00 US Iphone mini 489.00 US CA Product Price Warehouse **Galaxy -1 US ** Galaxy mini 595.99 CA
 
   <table width="200" border="1" cellpadding="0" cellspacing="0">
  <tr>
    <td width="100" colspan="3">NJ</td>
  </tr>
  <tr>
    <td width="100">Product</td>
    <td width="50">Price</td>
    <td width="50">Warehouse</td>
  </tr>
  <tr>
    <td>Ipad mini</td>
    <td>429.00</td>
  </tr>
  <tr>
    <td>Iphone mini</td>
    <td>429.00</td>
  </tr>
  <tr>
    <td width="100"  colspan="3">CA</td>
  </tr>
  <tr>
    <td width="100">Product</td>
    <td width="50">Price</td>
    <td width="50">Warehouse</td>
  </tr>
  <tr>
    <td>Galaxy</td>
    <td><B>-1</B></td>
  </tr>
  <tr>
    <td>Galaxy mini</td>
    <td>595.99</td>
  </tr>
</table>

Open in new window

0
I need to get the following format for serializing XML but not sure how best to handle this.  Element tag needed:

<create-by-workflow>
    <workflow-name>Customer</workflow-name>
</create-by-workflow>

Open in new window


Right now I have two classes as such:

public class WorkflowRequestDto
{
    [XmlElement(Constants.CreateByWorkflowTagName)] //this is     public const string CreateByWorkflowTagName = "create-by-workflow";
    public WorkflowNameDto WorkflowName { get; set; }
}

public class WorkflowNameDto
{
    [XmlElement(Constants.WorkflowNameTagName)] // this is     public const string WorkflowNameTagName = "workflow-name";
    public string WorkflowName { get; set; }
}

Open in new window


The JSON looks like this in Swagger:

"workflowName": {
    "workflowName": "string"
}

Open in new window


Is there a way to have it just be  "workflowName": "string" to the user so something along these lines although this does not work:

[XmlElement(Constants.CreateByWorkflowTagName)] 
[XmlElement(Constants.WorkflowNameTagName)]
public string WorkflowName { get; set; }

Open in new window

0
Amazon Web Services
LVL 12
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

I'm new to Python, trying to teach myself the language.  I'm using Best Buy's API to test. I'm not sure how to format the JSON data so it's readable in HTML/CSS format. Can you assist?

import requests

url = "https://api.bestbuy.com/v1/products%28customerReviewAverage%3E4%7CshippingWeight%3C50%29"

querystring = {"show":"upc,salePrice,customerReviewAverage,shippingWeight","apiKey":"XXXXXXXXXXXXXX","format":"json"}

response = requests.request("GET", url, params=querystring)

print(response.text)

Open in new window


Here is how the JSON response looks.

{"from":1,"to":10,"currentPage":1,"total":190579,"totalPages":19058,"queryTime":"0.203","totalTime":"0.230","partial":false,"canonicalUrl":"/v1/products(customerReviewAverage>4|shippingWeight<50)?show=upc,salePrice,customerReviewAverage,shippingWeight&format=json&apiKey=XXXXXXXXXXXX","products":[{"upc":"760514017023","salePrice":442.99,"customerReviewAverage":5.00,"shippingWeight":11.00},{"upc":"877929006358","salePrice":489.98,"customerReviewAverage":4.80,"shippingWeight":30.80},{"upc":"664254218934","salePrice":320.99,"customerReviewAverage":5.00,"shippingWeight":11.00},{"upc":"612934533280","salePrice":249.99,"customerReviewAverage":5.00,"shippingWeight":7.30},{"upc":"664254011221","salePrice":171.99,"customerReviewAverage":5.00,"shippingWeight":11.00},{"upc":"068888879262","salePrice":132.99,"customerReviewAverage":4.80,"shippingWeight":7.85},{"upc":"612934536229","salePrice":1062.99,"customerReviewAverage":4.70,"shippingWeight":28.50},{"upc":"093207100253","salePrice":499.98,"customerReviewAverage":4.40,"shippingWeight":35.89},{"upc":"865334000122","salePrice":202.99,"customerReviewAverage":4.20,"shippingWeight":9.99},{"upc":"865334000115","salePrice":237.99,"customerReviewAverage":4.30,"shippingWeight":9.99}]}

Open in new window

0
Hi,

I need to serialize vb.net code to json.

I need the json to look like this:
{
    "KalTid": [
        {
            "AnstNr": "1",
            "Datum": "2014-01-02",
            "RadId": "9b8412eb-77a4-4e0b-853e-fdd78edeac8a",
            "UseTid": true,
            "StartTid": "08:00",
            "SlutTid": "08:00",
            "Timmar": 8,
            "Dagar": 0,
            "TidKod": "SJK",
            "UseBrukare": true,
            "UseResEnhet1": false,
            "UseResEnhet2": false,
            "UseResEnhet3": false,
            "Brukare": "1",
            "ResEnhet1": "",
            "ResEnhet2": "",
            "ResEnhet3": "",
            "Registrerad": true,
            "CalcTyp": "",
            "Source": "PORTAL"
        }
    ]
}

Open in new window


The vb.net classes I'm using are looking like this:
Imports Microsoft.VisualBasic
Imports Newtonsoft.Json

Public Class KalTid
    <JsonProperty("AnstNr")>
    Public Property AnstNr As Long

    <JsonProperty("Datum")>
    Public Property Datum As DateTimeOffset

    <JsonProperty("RadId")>
    Public Property RadId As Guid

    <JsonProperty("UseTid")>
    Public Property UseTid As Boolean

    <JsonProperty("StartTid")>
    Public Property StartTid As String

    <JsonProperty("SlutTid")>
    Public Property SlutTid As String

    <JsonProperty("Timmar")>
    Public Property Timmar As Long

    <JsonProperty("Dagar")>
    Public Property Dagar As Long

    <JsonProperty("TidKod")>
    Public 

Open in new window

1
Hi,

I have this json request that I want to deserialize to an vb object. The json looks like this:
{
    "PerReg": [
        {
            "AnstNr": "1",
            "EfNamn": "Ljunggren",
            "FoNamn": "Siv",
            "Adress1": "Föreningsgatan 33",
            "Adress2": "",
            "PostNr": "447 34",
            "PostOrt": "VÅRGÅRDA",
            "LandAdr": "",
            "EpostPriv": "",
            "EpostArb": "",
            "Telefon": "0322-21298",
            "Mobil": "",
            "PersNr": "197901049283",
            "Signatur": "SL",
            "Befattning": "VD",
            "AnstDatum": "2001-09-01",
            "Slutat": false,
            "AvgDatum": "1899-12-30",
            "AnstTom": "1899-12-30",
            "Locked": false,
            "KalSchemaAuto": false,
            "UseOBRegel": false,
            "LockOBRegel": false,
            "OBRegelID": "",
            "UseArbSchema": true,
            "ArbSchema": "1",
            "Avdelning": "10",
            "KalUseAttest": true,
            "PerTyp": "LED",
            "AnstForm": "TV",
            "LoneForm": "MÅN",
            "LSSBerPerStart": "1899-12-30",
            "LSSBerPerInt": "",
            "LSSAnnanArbGiv": false,
            "LSSArbGivNamn": "",
            "LSSArbGivOrgnr": "",
            "UseSysGrad": true,
            "SysGrad": 100,
            "VeckArbTid": 40,
            "HelVeckTid": 40,
            "VeckArbDgr": 5,
            "DagArbTid": 8,
        

Open in new window

1
Hi I am selecting data from mysql database in node red: -

my database value are as follows:-
dbtime.png
Code in node red as follows: -
 msg.topic ="Select DeviceId, Line, outPIN, count,Time FROM mytab LIMIT 1"

Open in new window


In debug node i do get the time in different format and unable to store in different location of database.

9/17/2018, 4:59:29 PMnode: 4f250b1.97196f4
INSERT INTO mytable1 (DeviceId,Line,outPIN,count,Time) VALUES (?,?,?,?,?) : msg.payload : array[5]
[ 2, 1, "7", 1, "2018-09-16T13:28:24.000Z" ]
9/17/2018, 4:59:29 PMnode: d00874f1.76f388
msg : error
"Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2018-09-16T13:28:24.000Z' for column 'Time' at row 1"

Open in new window



How i can format the time back to normal 'yyyy-mm-dd hh:mm:ss'
0
HI, In node red, I did create node to count number of rows in "mytab" table.
msg.topic  = "SELECT COUNT(count) FROM mytab"; 

return msg;

Open in new window


Result from node-red after count total number of row in mytab table is.

SELECT COUNT(count) FROM mytab : msg.payload : string[20]
"[{"COUNT(count)":8}]"

I am trying to extract number of rows, in this case it is --> 8.

var obj = msg.payload;
var json = JSON.parse(obj);
var Device = json[0].COUNT(count);
msg.payload = Device;
return msg;

Open in new window


Error msg after executing pop up saying : -
function : (error)
"ReferenceError: count is not defined (line 4, col 28)"
0
I am extracting all data from mysql using Node-Red. Its give set of data in json format but with opening and closing quote to indicate as string format. I did try many ways to pull out particular line.

" [   {"ID":1,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:28:24.000Z","IdRef":1} ,
       {"ID":2,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:29:53.000Z","IdRef":1} ,
       {"ID":3,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:29:59.000Z","IdRef":1} ,
       {"ID":4,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:39:15.000Z","IdRef":1} ,
       {"ID":5,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:39:16.000Z","IdRef":1} ,
       {"ID":6,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:39:16.000Z","IdRef":1} ,
       {"ID":7,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:39:17.000Z","IdRef":1} ,
       {"ID":8,"DeviceId":2,"Line":1,"outPIN":"7","count":1,"Time":"2018-09-16T13:39:17.000Z","IdRef":1}   ] "

var obj = msg.payload;
var json = JSON.parse(obj);

var Device = json[1].Device_ID;
msg.payload = Device;
returen msg;

Open in new window


Error message as follows :

SELECT *FROM mytab : msg.payload : undefined
undefined
0

JSON

794

Solutions

802

Contributors

JavaScript Object Notation, or JSON, is human-readable text used when transmitting data objects consisting of attribute:value pairs between a server and a web application as an alternative to XML. JSON, while it is originally derived from JavaScript, is a language-independent data format. Code for parsing and generating JSON data is available in many programming languages.

Related Topics