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

Anonymous — Sat, 16 Nov 2024 05:30:39 GMT

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?

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

nfz11 — Tue, 25 Jun 2019 01:36:44 GMT

Try using the escape character \ before the dot.

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

Anonymous — Tue, 25 Jun 2019 02:09:17 GMT

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

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

nfz11 — Tue, 25 Jun 2019 06:11:15 GMT

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

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

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

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

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

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