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.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog