How filter by value in a field that looks as array of lists

Data Access Google BigQuery/Cloud
1

Hi,
Try to filter by value in a field that looks as array of lists.

SELECT targetId, diseaseId, textMiningSentences FROM open-targets-prod.platform.evidence as ev where datasourceId=‘europepmc’ and targetId=‘ENSG00000127318’ and diseaseId=‘EFO_0000274’ and textMiningSentences.section=‘abstract’ LIMIT 1000

get error:
Field name section does not exist in STRUCT<list ARRAY<STRUCT<element STRUCT<dEnd INT64, dStart INT64, section STRING, …>>>> at [1:212]

2

Hi There,

The problem is that you cannot apply a filter on nested structures, you need to “unnest” fist to get a flat structure like this:

SELECT
  targetId,
  diseaseId,
  textMiningSentences,
FROM
  open-targets-prod.platform.evidence AS ev,
  UNNEST(textMiningSentences.list) as t
WHERE
  datasourceId="europepmc"
  AND targetId="ENSG00000127318"
  AND diseaseId="EFO_0000274"
  AND t.element.section = "abstract"
LIMIT 1000

The above solution only provides matches in the abstracts, not in other sections. But it can give you some inspiration on how to go further: by joining this table by targetId, diseaseId, pmid, you can get all textMiningSentences for these publications.