0

Is it possible to return the query results from Spanner to a JSON document? For example, if the query is to "read all transactions for this account", and return those row / record results into a single JSON strong or document, is this possible ? If so, what is the size limitation? ie how many transaction rows can this hold ? example - of there wee 400 transactions in a table, could those all go into a JSON doc or string?

https://cloud.google.com/spanner/docs/json_functions

Gordon Baird
  • 47
  • 2
  • 4
  • The URL you provide only talks about how to retrieve data stored in JSON formatted string in the query, not the other way around (turning the result into a JSON string. Also, is it possible that you read all the rows first and then convert the whole result into a JSON using json encoder library? – Hailong Wen Sep 18 '20 at 20:25
  • yes, possible.. do you have any thoughts on which library is best / fastest ? – Gordon Baird Sep 18 '20 at 20:49

1 Answers1

0

As Hailong comments, the link you shared retrieved data stored in Json formatted string. Cloud Spanner does not provide a native way to perform this task. If you want to do it, you need to implement it in your code. For example, you can take a look at this other question as a reference. But it will be depend of the language you want to use.

On the other hand, using a gcloud command you can perform it using gcloud spanner databases execute-sql and using the --format flag to select the json format as --format=json You can take a look at here and here for more information about it.

Samuel Romero
  • 1,233
  • 7
  • 12
  • if the goal is to replicate data between Cloud Spanner and Google Firestore, is there a simpler or better approach ? – Gordon Baird Sep 19 '20 at 19:55
  • Hi Gordon, since this a bit different, I would recommend you to open a new question about this concern. Nevertheless, as a quick response. The answer is as the above, Spanner does not provide any native way to perform a task like this, so, it would be necessary you implement an app that perform it. Cloud Spanner allows you to export a data base to [Avro](https://cloud.google.com/spanner/docs/export) and the export is to json files but I am not sure that this helps you – Samuel Romero Sep 22 '20 at 17:25