Parsing Mendeley Data

27 views
Skip to first unread message

Amit Nath

unread,
Oct 13, 2022, 4:00:12 AM10/13/22
to OpenRefine
Hi OR community!

I have a question about parsing results from the Mendeley API.

The JSON formatted data looks like that: 

[{"title":"Food in the Anthropocene: the EAT–Lancet Commission on healthy diets from sustainable food systems","type":"generic","authors":[{"first_name":"Walter","last_name":"Willett","scopus_author_id":"56803239300"},{"first_name":"Johan","last_name":"Rockström","scopus_author_id":"6603746245"},{"first_name":"Brent","last_name":"Loken","scopus_author_id":"57328140400"},{"first_name":"Marco","last_name":"Springmann","scopus_author_id":"55366816500"},{"first_name":"Tim","last_name":"Lang","scopus_author_id":"7202119824"},{"first_name":"Sonja","last_name":"Vermeulen","scopus_author_id":"7007115092"},{"first_name":"Tara","last_name":"Garnett","scopus_author_id":"57208462432"},{"first_name":"David","last_name":"Tilman","scopus_author_id":"7005020351"},{"first_name":"Fabrice","last_name":"DeClerck","scopus_author_id":"57207521843"},{"first_name":"Amanda","last_name":"Wood","scopus_author_id":"49061583300"},{"first_name":"Malin","last_name":"Jonell","scopus_author_id":"55857925300"},{"first_name":"Michael","last_name":"Clark","scopus_author_id":"35333958400"},{"first_name":"Line J.","last_name":"Gordon","scopus_author_id":"7202829709"},{"first_name":"Jessica","last_name":"Fanzo","scopus_author_id":"6506547869"},{"first_name":"Corinna","last_name":"Hawkes","scopus_author_id":"8589816500"},{"first_name":"Rami","last_name":"Zurayk","scopus_author_id":"6701381333"},{"first_name":"Juan A.","last_name":"Rivera","scopus_author_id":"7202308740"},{"first_name":"Wim","last_name":"De Vries","scopus_author_id":"7102036739"},{"first_name":"Lindiwe","last_name":"Majele Sibanda","scopus_author_id":"6504275561"},{"first_name":"Ashkan","last_name":"Afshin","scopus_author_id":"57217465455"},{"first_name":"Abhishek","last_name":"Chaudhary","scopus_author_id":"56448098500"},{"first_name":"Mario","last_name":"Herrero","scopus_author_id":"34770911400"},{"first_name":"Rina","last_name":"Agustina","scopus_author_id":"57214141404"},{"first_name":"Francesco","last_name":"Branca","scopus_author_id":"55778919800"},{"first_name":"Anna","last_name":"Lartey","scopus_author_id":"6603203229"},{"first_name":"Shenggen","last_name":"Fan","scopus_author_id":"7402678398"},{"first_name":"Beatrice","last_name":"Crona","scopus_author_id":"57203064581"},{"first_name":"Elizabeth","last_name":"Fox","scopus_author_id":"57075787500"},{"first_name":"Victoria","last_name":"Bignet","scopus_author_id":"57196717898"},{"first_name":"Max","last_name":"Troell","scopus_author_id":"6701524165"},{"first_name":"Therese","last_name":"Lindahl","scopus_author_id":"25936582900"},{"first_name":"Sudhvir","last_name":"Singh","scopus_author_id":"37113020600"},{"first_name":"Sarah E.","last_name":"Cornell","scopus_author_id":"55096139600"},{"first_name":"K.","last_name":"Srinath Reddy","scopus_author_id":"57210953125"},{"first_name":"Sunita","last_name":"Narain","scopus_author_id":"57224745389"},{"first_name":"Sania","last_name":"Nishtar","scopus_author_id":"57783173700"},{"first_name":"Christopher J.L.","last_name":"Murray","scopus_author_id":"55481130700"}],"year":2019,"source":"The Lancet","identifiers":{"pmid":"30660336","scopus":"2-s2.0-85060688048","pii":"S0140673618317884","pui":"2001516902","issn":"1474547X","sgr":"85060688048","doi":"10.1016/S0140-6736(18)31788-4"},"id":"7f2d2536-aaad-3e8a-b519-0593ef101814","link":"https://www.mendeley.com/catalogue/7f2d2536-aaad-3e8a-b519-0593ef101814/","reader_count":5816,"reader_count_by_academic_status":{"Student > Postgraduate":138,"Professor > Associate Professor":115,"Researcher":818,"Student > Master":893,"Student > Ph. D. Student":716,"Professor":159,"Student > Bachelor":717,"Student > Doctoral Student":211,"Lecturer":102,"Other":253,"Librarian":11,"Lecturer > Senior Lecturer":35,"Unspecified":78},"reader_count_by_user_role":{"Student > Postgraduate":138,"Professor > Associate Professor":115,"Researcher":818,"Student > Master":893,"Student > Ph. D. Student":716,"Professor":159,"Student > Bachelor":717,"Student > Doctoral Student":211,"Lecturer":102,"Other":253,"Librarian":11,"Lecturer > Senior Lecturer":35,"Unspecified":78},"reader_count_by_subject_area":{"Philosophy":10,"Decision Sciences":9,"Chemical Engineering":33,"Design":30,"Materials Science":15,"Pharmacology, Toxicology and Pharmaceutical Science":28,"Energy":29,"Computer Science":44,"Chemistry":62,"Nursing and Health Professions":410,"Neuroscience":35,"Social Sciences":395,"Sports and Recreations":28,"Earth and Planetary Sciences":84,"Physics and Astronomy":21,"Engineering":162,"Agricultural and Biological Sciences":839,"Medicine and Dentistry":385,"Veterinary Science and Veterinary Medicine":21,"Arts and Humanities":56,"Environmental Science":546,"Psychology":76,"Economics, Econometrics and Finance":154,"Mathematics":9,"Linguistics":8,"Biochemistry, Genetics and Molecular Biology":188,"Immunology and Microbiology":22,"Business, Management and Accounting":102,"Unspecified":83},"reader_count_by_subdiscipline":{"Sports and Recreations":{"Sports and Recreations":28},"Psychology":{"Psychology":76},"Mathematics":{"Mathematics":9},"Pharmacology, Toxicology and Pharmaceutical Science":{"Pharmacology, Toxicology and Pharmaceutical Science":28},"Environmental Science":{"Environmental Science":546},"Chemical Engineering":{"Chemical Engineering":33},"Design":{"Design":30},"Engineering":{"Engineering":162},"Neuroscience":{"Neuroscience":35},"Energy":{"Energy":29},"Immunology and Microbiology":{"Immunology and Microbiology":22},"Computer Science":{"Computer Science":44},"Agricultural and Biological Sciences":{"Agricultural and Biological Sciences":839},"Linguistics":{"Linguistics":8},"Business, Management and Accounting":{"Business, Management and Accounting":102},"Biochemistry, Genetics and Molecular Biology":{"Biochemistry, Genetics and Molecular Biology":188},"Materials Science":{"Materials Science":15},"Medicine and Dentistry":{"Medicine and Dentistry":385},"Social Sciences":{"Social Sciences":395},"Decision Sciences":{"Decision Sciences":9},"Physics and Astronomy":{"Physics and Astronomy":21},"Unspecified":{"Unspecified":83},"Arts and Humanities":{"Arts and Humanities":56},"Chemistry":{"Chemistry":62},"Earth and Planetary Sciences":{"Earth and Planetary Sciences":84},"Economics, Econometrics and Finance":{"Economics, Econometrics and Finance":154},"Nursing and Health Professions":{"Nursing and Health Professions":410},"Philosophy":{"Philosophy":10},"Veterinary Science and Veterinary Medicine":{"Veterinary Science and Veterinary Medicine":21}},"group_count":589}]

I was trying to parse the "reader_count" through the GREL function "value.parseJson().reader_count" and it was not working. Please guide me... 

Thanks
Amit Nath







Error-Screenshot.JPG

Owen Stephens

unread,
Oct 13, 2022, 6:54:48 AM10/13/22
to OpenRefine
The JSON structure here is an object inside an array. This means that to extract a particular value from the object (e.g. reader_count) you first need to select the right object from the array.

If you know there will only ever be a single object in the array, or that you always want the first object in the array, you could use:
value.parseJson()[0].reader_count

However, if you aren't sure how many objects might appear in the array and you want the reader_count for each object from the array you can use:
forEach(value.parseJson(),v,v.reader_count).join("|")

This latter expression will work in the case of a single object or for multiple objects in the array

Best wishes

Owen

Amit Nath

unread,
Oct 14, 2022, 2:27:25 AM10/14/22
to openr...@googlegroups.com
Thanks, Sir.
It's working now.




Regards,
Amit Nath




--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openrefine/89a254d2-a63a-4da6-9189-cc2a4f0259f0n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages