1

I want a combined query suitable for selecting rows where lang = en i.e. language is equal to English and removing the other rows while I convert this file from JSON to CSV.

json2csv -i Downloads/30.json -f id,text,lang -o Downloads/30.csv

30.json

{"created_at":"Sun Apr 01 09:00:00 +0000 2018","id":980369176291262464,"id_str":"980369176291262464","text":"RT @Q_cupid: \u0e01\u0e23\u0e30\u0e41\u0e2a\u0e43\u0e2a\u0e48\u0e0a\u0e38\u0e14\u0e44\u0e17\u0e22\u0e21\u0e32\u0e41\u0e23\u0e07\u0e2d\u0e31\u0e19\u0e19\u0e35\u0e49\u0e40\u0e02\u0e49\u0e32\u0e43\u0e08 \u0e41\u0e15\u0e48\u0e17\u0e35\u0e48\u0e44\u0e21\u0e48\u0e40\u0e02\u0e49\u0e32\u0e43\u0e08\u0e04\u0e37\u0e2d\u0e04\u0e19\u0e17\u0e35\u0e48\u0e01\u0e25\u0e49\u0e32\u0e44\u0e1b\u0e02\u0e42\u0e21\u0e22\u0e0a\u0e38\u0e14\u0e44\u0e17\u0e22\u0e17\u0e35\u0e48\u0e40\u0e04\u0e49\u0e32\u0e40\u0e2d\u0e32\u0e44\u0e27\u0e49\u0e44\u0e1b\u0e41\u0e01\u0e49\u0e1a\u0e19\u0e15\u0e32\u0e21\u0e28\u0e32\u0e25\u0e19\u0e35\u0e48\u0e41\u0e2b\u0e25\u0e30 \u0e2d\u0e35\u0e40\u0e27\u0e07\u0e07 \u0e02\u0e42\u0e21\u0e22\u0e44\u0e1b\u0e40\u0e01\u0e37\u0e2d\u0e1a100\u2026","source":"\u003ca href=\"http:\/\/twitter.com\/download\/android\" rel=\"nofollow\"\u003eTwitter for Android\u003c\/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":782859283733983233,"id_str":"782859283733983233","name":"mystar\ud83c\udf1fjaemin\ud83c\udf1f","screen_name":"mljm1920","location":null,"url":null,"description":"\u0e40\u0e1b\u0e47\u0e19\u0e21\u0e35\u0e4a\u0e17\u0e35\u0e48\u0e23\u0e31\u0e01\u0e19\u0e49\u0e2d\u0e07\u0e41\u0e08\u0e21\u0e21\u0e32\u0e01\u0e17\u0e35\u0e48\u0e2a\u0e38\u0e14\ud83d\ude18\ud83d\ude18 #JAEMIN  \ud83c\udf51 #EXO #NCTDREAM #NCT \u0e0a\u0e34\u0e1b\u0e40\u0e1b\u0e2d\u0e23\u0e4c #markmin \u0e40\u0e1b\u0e47\u0e19\u0e2b\u0e25\u0e31\u0e01 #HunHan #NoRen #ChanLe #SungLe","translator_type":"none","protected":false,"verified":false,"followers_count":205,"friends_count":2155,"listed_count":0,"favourites_count":17155,"statuses_count":9823,"created_at":"Mon Oct 03 08:26:12 +0000 2016","utc_offset":null,"time_zone":null,"geo_enabled":false,"lang":"th","contributors_enabled":false,"is_translator":false,"profile_background_color":"F5F8FA","profile_background_image_url":"","profile_background_image_url_https":"","profile_background_tile":false,"profile_link_color":"1DA1F2","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http:\/\/pbs.twimg.com\/profile_images\/980103235007492096\/Xx-SpFsJ_normal.jpg","profile_image_url_https":"https:\/\/pbs.twimg.com\/profile_images\/980103235007492096\/Xx-SpFsJ_normal.jpg","profile_banner_url":"https:\/\/pbs.twimg.com\/profile_banners\/782859283733983233\/1521042399","default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"retweeted_status":

Required output: 30.csv

In output I need rows only with lang column value =en

  • Can you add a sample input (from 30.json) and required output? – Haxiel Jan 3 at 10:50
  • {"created_at":"Sun Apr 01 06:30:00 +0000 2018","id_str":"980331427534262272","text":"RT YahBoyCourage: we strictly listening to AM radio now","source":"\u003ca href=\"http:\/\/twitter.com\/download\/iphone\" rel=\"nofollow\"\u003eTwitter for iPhone\u003c\/a\u003e","in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":64939967,"id_str":"64939967","name":"Cesar \ud83c\uddf2\ud83c\uddfd\ud83c\uddf5\ud83c\uddea","screen_name":"CZR93","location":"Richmond, CA", Couldnt add more due to word limit @Haxiel – DaZzLeR Jan 3 at 10:59
  • 1
    Please edit the question and add it there instead. – Haxiel Jan 3 at 11:02
  • It's unclear how you would want to do the filtering. You say you want to filter on lang and only use data where lang is en. The only field that is lang is part of the user object in the incomplete JSON document fragment that you show. Does this mean that you want to filter out all non-English users? The data that you show also only shows a single message. Does the real data contain an array of messages? Your required output also seems to show non-English data. – Kusalananda Jan 3 at 11:38
  • Yes, I wanted to remove all non-English users. Yes, I have a huge amount of data. @Kusalananda – DaZzLeR Jan 4 at 9:37
2

With jq (https://stedolan.github.io/jq/), you could do this:

jq -r '{ id, text, lang: .user.lang } | select (.lang == "en") | [ .id, .text, .lang ] | @csv' 30.json

This should get you the required output:

980369176291262500,"RT @Q_cupid: กระแสใส่ชุดไทยมาแรงอันนี้เข้าใจ แต่ที่ไม่เข้าใจคือคนที่กล้าไปขโมยชุดไทยที่เค้าเอาไว้ไปแก้บนตามศาลนี่แหละ อีเวงง ขโมยไปเกือบ100…","en"

To write the output to a file, simply redirect it:

jq -r '{ id, text, lang: .user.lang } | select (.lang == "en") | [ .id, .text, .lang ] | @csv' 30.json > output.csv

Note that I have modified the example JSON from your question to be a valid one. You seem to be discarding the bulk of the data, so this should not cause any problems.

  • Thank you for the code but it dumped all the data onto my screen while I want output as a CSV file. Where am I supposed to specify that I want rows with language English only? @Haxiel – DaZzLeR Jan 4 at 6:16
  • @DaZzLeR I missed out on the conditional selection requirement earlier. It's fixed now; see my edit. – Haxiel Jan 4 at 6:48
  • @DaZzLeR If my answer works for you, please do remember to accept it (by clicking the tick mark next to the answer). This action grants reputation to both of us. – Haxiel Jan 4 at 7:26
  • How can I combine these two queries? jq -r '{ text, lang: .user.lang } | select (.lang == "en") | [ .text, .lang ] | @csv' 57.json > output.csv The above query gives me the rows which are in English language and removes the rest of them. cut -d, -f2 --complement test.csv > output.csv and this query removes the language column after the need of language column is fulfilled. What should I do to do these 2 operations in a single query? Can that be done? – DaZzLeR Jan 10 at 8:20
  • can you combine these queries? – DaZzLeR Jan 10 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.