How to get first object from JSON array in Talend

Question

I have data in the MongoDB database where one of the columns is JSON Array. Now I want to retrieve only the first object from that JSON Array column.

Input_Column :

**[{"id" : "25",
"name" : "spot1" ,
"node" : "start_node", 
"order" : "1",
"status" : "completed", 
"location" : "gate1"},
{"name" : "spot2", 
"order" : "2" },
{ "name" : "spot3", 
"node" : "start_node", 
"status" : "pending" }]**

Required output_column :

 `status:`

 completed

I need only the first value from the array. I tried using the tExtractJSONFields component in Talend. And set the JSON loop as "$.status". But I got the output as

`status:`

completed
null
pending

AFTER EDIT :

[{"id" : "25",
"name" : "spot1" ,
"node" : "start_node", 
"order" : "1",
"status" : "completed" ,
"location" : "gate1"},
{"name" : "spot2", 
"order" : "2" },
{ "name" : "spot3", 
"node" : "start_node", 
"status" : "pending" } ]


[{"id" : "26",
"name" : "pull1" ,
"node" : "start_node", 
"order" : "3",
"status" : "arrived" ,
"location" : "gate3"},
{"name" : "pull2", 
"order" : "4" },
{ "name" : "pull3", 
"node" : "end_node", 
"status" : "pending" } ]

[{"id" : "27",
"name" : "task1" ,
"node" : "start_node", 
"order" : "5",
"status" : "pending" ,
"location" : "gate12"},
{"name" : "task2", 
"order" : "6" },
{ "name" : "task3", 
"node" : "end_node", 
"status" : "pending" } ]

expected output :

status 

completed
arrived
pending

Check this post : stackoverflow.com/questions/47590235/jsonpath-return-first-find . it seems that your solution is in there (unvoted though) — Corentin
– Corentin, Commented May 27, 2021 at 14:56
@Corentin I tried "$[0].status" expression in tExtractJSOFields but I get only null values — pavithra
– pavithra, Commented May 28, 2021 at 4:50

Amine Ben Khelifa · Accepted Answer · 2021-05-27 12:42:58Z

0

I think that this is not a valid json you have post

I corrected your json to be like this

You can test it here : https://jsonformatter.curiousconcept.com/#

[{"id" : "25",
"name" : "spot1" ,
"node" : "start_node", 
"order" : "1",
"status" : "completed" ,
"location" : "gate1"},
{"name" : "spot2", 
"order" : "2" },
{ "name" : "spot3", 
"node" : "start_node", 
"status" : "pending" } ]

Try to put your json in a file at first time
-> then in the metadata

--> drag & drop your file json (Metadata)

In your job just put this structure

javaFlex is configured as such

In the advanced settings don't forget to put :

import java.util.ArrayList;

Output would be :

Starting job Test_ at 14:36 27/05/2021. [statistics] connecting to socket on port 3903 [statistics] connected completed [statistics] disconnected

Job Test_ ended at 14:36 27/05/2021. [exit code = 0]

edited May 27, 2021 at 12:42

answered May 27, 2021 at 10:54

Amine Ben Khelifa

7656 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

pavithra Over a year ago

I need to get the first value from status. How do I achieve that? status = completed

Amine Ben Khelifa Over a year ago

Just edited my answer ! this is how you could achieve it

pavithra Over a year ago

Thank you so much. This works for one record. I have multiple records where the first element from all the JSON arrays must be retrieved.

Amine Ben Khelifa Over a year ago

Just adapt the method using the other columns and delete the first element

pavithra Over a year ago

I have edited my ques, please check. Thanks

|

Collectives™ on Stack Overflow

How to get first object from JSON array in Talend

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related