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

[Anonymous]

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?

[nfz11]

Try using the escape character \ before the dot.

[Anonymous]

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

[nfz11]

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.

TestJsonParseSpecialCharacters.zip