I have a nested repeated structure, the repeated structure is of variable length. For example, it could be a person object with a repeated structure that holds cities the person has lived in. I'd like to find the last item in that list say to find current city person lives in. Is there an easy way to do this, I tried looking around jsonpath functions but I'm not sure how to use it with "within". Any help please?
Asked
Active
Viewed 2,386 times
1 Answers
7
1) You can use LAST and WITHIN
SELECT
FIRST(cell.value) within record ,
LAST(cell.value) within record
FROM [publicdata:samples.trigrams]
where ngram = "! ! That"
2) or if you want something more advanced you can use POSITION
POSITION(field) - Returns the one-based, sequential position of field within a set of repeated fields.
You can check the samples from trigrams (click on Details to see the unflatten schema) https://bigquery.cloud.google.com/table/publicdata:samples.trigrams?pli=1
And when you run POSITION, you get the ordering of that field.
SELECT
ngram,
cell.value,
position(cell.volume_count) as pos,
FROM [publicdata:samples.trigrams]
where ngram = "! ! That"
Now that you have the position, you can query for last one.
Pentium10
- 204,586
- 122
- 423
- 502
-
that's great - is it also possible to have the first item and last item in same SELECT clause? – opensourcegeek Feb 18 '15 at 18:23
-
yes you can have that, or you can use a subquery every time – Pentium10 Feb 18 '15 at 19:20
-
Do you mean writing subquery for fetching first and another subquery to fetch last items? – opensourcegeek Feb 19 '15 at 09:41
-
@opensourcegeek if you would given a try you would figure it out by now, see my updates answer, it works in the same query. – Pentium10 Feb 19 '15 at 09:45