JSONPath read the dot (.) as child seperator in tFileInputJSON

Highlighted
Five Stars

JSONPath read the dot (.) as child seperator in tFileInputJSON

Hi everyone,

 

I have 2 questions regarding the tFileInputJSON.

 

1) How to make the JSONPath can differentiate the dot (.) between the child seperator and the child name itself.

For example in the picture, I already Tried to put the square bracket and also single quote. But there still error cannot find the path.

 

2) How can I select the first data from the JSON path besides using [0] method? For example => "store.book[0]" . Is there any other method I can use to extract the first data?

Nine Stars

Re: JSONPath read the dot (.) as child seperator in tFileInputJSON

Try using the escape character \ before the dot.

--
Please give Kudos and mark topics as solved where appropriate.
Five Stars

Re: JSONPath read the dot (.) as child seperator in tFileInputJSON

I tried already but it says that invalid escape sequence. Is there any other ways to escape the character?

Nine Stars

Re: JSONPath read the dot (.) as child seperator in tFileInputJSON

JSONPath does not support node names with a dot.  There is no way to escape them or get around this in pure JSONPath, it is just illegal syntax.  So your JSON as it stands now cannot be parsed by JSONPath in Talend. 

https://community.talend.com/t5/Design-and-Development/Parsing-JSON-columns-having-dot/td-p/131734

 

You need curly brackets around your whole JSON string.  Here is your JSON formatted and what I used in testing:

 

 

{
  "Time Series (Daily)": {
    "2019-06-14": {
"1. open": "132.2600", "2. high": "133.7900" } } }

 

 

It is also bad practice to have space in your node names and to have node names start with a number, but the [''] syntax gets around that.

 

Here is the result of some tests I ran on your data and some well-formed sample data:

 

Starting job TestJsonParseSpecialCharacters at 02:43 25/06/2019.

[statistics] connecting to socket on port 3905
[statistics] connected
.------------------------------------------------------------------------------------------------.
|                                         #1. tLogRow_1                                          |
+-------+----------------------------------------------------------------------------------------+
| key   | value                                                                                  |
+-------+----------------------------------------------------------------------------------------+
| json  | {"Time Series (Daily)" : {"2019-06-14":{"1. open": "132.2600","2. high": "133.7900"}}} |
| title | JSONPath: won't work with node names with dot.  Best attempt here.                     |
+-------+----------------------------------------------------------------------------------------+

.--------------------------------------------------------.
|                     #1. tLogRow_2                      |
+----------+---------------------------------------------+
| key      | value                                       |
+----------+---------------------------------------------+
| Category | {"1. open":"132.2600","2. high":"133.7900"} |
+----------+---------------------------------------------+

.-----------------------------------------.
|              #1. tLogRow_3              |
+-------+---------------------------------+
| key   | value                           |
+-------+---------------------------------+
| json  | {"a":{"b":{"c": "d","e": "f"}}} |
| title | JSONPath on well-formed data    |
+-------+---------------------------------+

.------------------.
|  #1. tLogRow_4   |
+----------+-------+
| key      | value |
+----------+-------+
| Category | d     |
+----------+-------+

.-----------------------------------------.
|              #1. tLogRow_7              |
+-------+---------------------------------+
| key   | value                           |
+-------+---------------------------------+
| json  | {"a":{"b":{"c": "d","e": "f"}}} |
| title | XPath on well-formed data       |
+-------+---------------------------------+

.------------------.
|  #1. tLogRow_8   |
+----------+-------+
| key      | value |
+----------+-------+
| Category | d     |
+----------+-------+

[statistics] disconnected

Job TestJsonParseSpecialCharacters ended at 02:43 25/06/2019. [exit code=0]

 

You can try playing around with XPath. It may be more forgiving than JSONPath for your malformed data.

 

I am also attaching the export of my test job if you want to look at the details.

 

--
Please give Kudos and mark topics as solved where appropriate.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now

How to Modernize Your Cloud Platform for Big Data Analytics With Talend and Micr...

Learn how<SPAN>to modernize your Cloud Platform for Big Data Analytics with Talend and Microsoft Azure</SPAN>

Read