eQTL data extraction from database JSON file

Question

I have a long list of JSON data, with repeats of contents similar to followings.

Due to the original JSON file is too long, I will just shared the hyperlinks here. This is a result generated from a database called RegulomeDB.

Direct link to the JSON file

I would like to extract specific data (eQTLs) from "method": "eQTLs" and "value": "xxxx", and put them into 2 columns (tab delimited) exactly like below. Note: "value":"xxxx" is extracted right after "method": "eQTLs"is detected.

eQTLs   firstResult, secondResult, thirdResult, ...

In this example, the desired output is:

eQTLs   EIF3S8, EIF3CL

I've tried using a python script but was unsuccessful.

import json
with open('file.json') as f:
    f_json = json.load(f)
    print 'f_json[0]['"method": "eQTLs"'] + "\t" + f_json[0]["value"]

Thank you for your kind help.

Answer 1

Here a jq solution if you can use it:

jq -r --arg f "eQTLs" '$f + "\t" + ([."@graph"[] | select(.method == $f) | .value] | join(", "))' file.json

Explanations:

• -r: Raw string result (so result string will be without between ")
• --arg name value: Your filter on eQTLs method value
• Search in @graph array elements with []
• Select only array element with method attribute equals to eQTLs (in $f argument)
• The result, 2 in this case, are put in a array [...] and join with string ,
• $f string, a tabulation "\t" and the join result are concatenated to one string with + operators

Result:

eQTLs   EIF3S8, EIF3CL

Answer 2

It's a little hard to know what your goal is here based on the sample code, but I do find that your JSON snippet does not match any spec. I was able to edit it to add brackets to make the entries elements of a list:

[
  {
  "biosample_ontology": {
    "@id": "https://www.encodeproject.org/biosample-types/primary_cell_CL_0000576/",
    "@type": [
      "BiosampleType",
      "Item"
    ],
    "aliases": [],
    "cell_slims": [
      "hematopoietic cell",
      "monocyte",
      "myeloid cell",
      "leukocyte"
    ],
    "classification": "primary cell",
    "dbxrefs": [],
    "developmental_slims": [
      "mesoderm"
    ],
    "name": "primary_cell_CL_0000576",
    "organ_slims": [
      "blood",
      "bodily fluid"
    ],
    "references": [],
    "schema_version": "1",
    "status": "released",
    "synonyms": [],
    "system_slims": [
      "immune system"
    ],
    "term_id": "CL:0000576",
    "term_name": "monocyte",
    "uuid": "20685264-1187-4c5c-b4ce-dc85ea53fc11"
  },
  "chrom": "chr11",
  "dataset": "https://www.encodeproject.org/annotations/ENCSR464XOM/",
  "dataset_rel": "/annotations/ENCSR464XOM/",
  "documents": [],
  "end": 59936979,
  "file": "ENCFF814NRI",
  "method": "eQTLs",
  "start": 59936978,
  "strand": null,
  "targets": [],
  "value": "MS4A4A"
},

    {
  "biosample_ontology": {
    "@id": "https://www.encodeproject.org/biosample-types/primary_cell_CL_0000576/",
    "@type": [
      "BiosampleType",
      "Item"
    ],
    "aliases": [],
    "cell_slims": [
      "hematopoietic cell",
      "monocyte",
      "myeloid cell",
      "leukocyte"
    ],
    "classification": "primary cell",
    "dbxrefs": [],
    "developmental_slims": [
      "mesoderm"
    ],
    "name": "primary_cell_CL_0000576",
    "organ_slims": [
      "blood",
      "bodily fluid"
    ],
    "references": [],
    "schema_version": "1",
    "status": "released",
    "synonyms": [],
    "system_slims": [
      "immune system"
    ],
    "term_id": "CL:0000576",
    "term_name": "monocyte",
    "uuid": "20685264-1187-4c5c-b4ce-dc85ea53fc11"
  },
  "chrom": "chr11",
  "dataset": "https://www.encodeproject.org/annotations/ENCSR464XOM/",
  "dataset_rel": "/annotations/ENCSR464XOM/",
  "documents": [],
  "end": 59936979,
  "file": "ENCFF814NRI",
  "method": "eQTLs",
  "start": 59936979,
  "strand": null,
  "targets": [],
  "value": "MS4A4B"
}
]

When I did this, I was able to slightly modify your code to print out each element's method key, value pair:

import json
with open('/Users/max.press/Downloads/file.json') as f:
    f_json = json.load(f)
    for i in range(len(f_json)):
        print(f_json[i]["method"] + "\t" + f_json[i]["value"])

% python ~/Downloads/readfile.py
# prints this:
eQTLs   MS4A4A
eQTLs   MS4A4B

Answer 3

A (very) simple Python solution:

The script file readfile.py:

#! /usr/bin/python3

import sys
import json

filename=sys.argv[1]
filter=sys.argv[2]

results=[]
with open(filename) as json_file:
    json_content = json.load(json_file)
    for json_graph_element in json_content["@graph"]:
        if json_graph_element["method"] == filter:
            results.append(json_graph_element["value"])
print("%s\t%s" % (filter, ", ".join(results)))

Make the script executable:

chmod +x readfile.py

Use the script:

./readfile.py /path/to/the/file.json  eQTLs

Result:

eQTLs   EIF3S8, EIF3CL