3

I would like to save the result set of the following query to a table.

SELECT

g, NEST(x) AS a

FROM 

(SELECT 1 AS g, 10 AS x), (SELECT 1 AS g, 20 AS x), (SELECT 1 AS g, 30 AS x), 
(SELECT 2 AS g, 100 AS x), (SELECT 2 AS g, 200 AS x)  

GROUP BY g

I use the web query composer with the options "Write if empty", "Allow Large Results: yes", "Flatten Results: no" and "Query priority: Interactive". I don't want to flatten query results.

Query failes: "Error: Unexpected. Please try again."

When I created a table with the following schema and selected the option "Append to table", I received the same error.

{
    "fields": [
        {
            "name": "g",
            "type": "integer"
        },
        {
            "name": "a",
            "type": "integer",
            "mode": "repeated"
        }
    ]
}

Thank you for any advice.

Nathan
  • 63
  • 4
  • I was just looking into this right now. Even added to paid enterprise support as a case, but received no answer yet. – Pentium10 Nov 14 '14 at 15:19

1 Answers1

1

This is a known issue with the NEST function and flattenResults set to false: the two aren't compatible with each other at present. We'd like to fix this but I don't have an ETA as the underlying issue is nontrivial.

If you have an existing table with a repeated field, you should be able to successfully SELECT it and output it as a repeated field using flattenResults set to false.

Danny Kitt
  • 3,241
  • 12
  • 22
  • We'd love to transform a table which has `RECORD` type columns into another table. Something from `record1.timestamp` should be moved to `record2.timestamp` (and we no longer need `record1.timestamp`) How to proceed with this kind of query/destinationTable? – Pentium10 Nov 14 '14 at 19:51
  • I don't think changing the name of a record or the parent record for a field using unflattened results is currently supported. We do have longer-term plans for launching support for this type of schema transformation in the future however. – Danny Kitt Nov 14 '14 at 20:22
  • @DannyKitt I completely understand the first paragraph of your answer. I am not sure with the seconds paragraph. Is there currently a way to transform table containing two rows `{"p": 1, "a": 10}`, `{"p": 1, "a": 20}` to a table with repeated field containing one row `{"p": 1, "a": [10, 20]}`? – Nathan Nov 14 '14 at 20:31
  • @Nathan Given that `NEST` does not work, I don't think there is a way to do that for an integer field. But here's a workaround for strings: `select a, SPLIT(GROUP_CONCAT(b), ',') from (select 'z' as a, 'y' as b), (select 'z' as a, 'x' as b) group by a;` – Danny Kitt Nov 14 '14 at 21:18
  • @DannyKitt suppose that we want to have these now, and we already run a production app, what are our options? – Pentium10 Nov 14 '14 at 22:16
  • @Pentium10 Until the features Danny alluded to are available, you will likely need to use some form of export/transform/import to update your existing table(s). We also can't provide guidance at this time on when the new functionality will be released. – shollyman Nov 14 '14 at 23:19