schema of an existing table in JSON format using bigquery select statement in bigquery

Question

Can we write select statement to get schema of an existing table in JSON format or can we write bigquery to

Select statement should generate the json format in the result set. (or in bigquery - how to Select existing table schema as JSON format)

example - [ { "name": "year", "type": "INTEGER", "mode": "NULLABLE" }, { "name": "month", "type": "INTEGER", "mode": "NULLABLE" }, { "name": "project_bytes", "type": "BYTES", "mode": "NULLABLE" }, { "name": "lang", "type": "STRING", "mode": "NULLABLE" } ]

Samuel · Accepted Answer · 2021-07-30 05:39:07Z

4

Metadata of datasets and tables and the columns can be accessed by a query. https://cloud.google.com/bigquery/docs/information-schema-intro

For yourdataset all columns of all tables can be show with their schema:

SELECT * FROM yourdataset.INFORMATION_SCHEMA.COLUMNS
where table_name="yourtablename"

Converting this into a JSON string is also possible:

Select table_name, TO_JSON_STRING(array_agg(schema_col)) schema
from (
SELECT
  table_name,column_name,
   STRUCT(T.column_name AS name,
    any_value(T.data_type) AS type,
    IF (any_Value(T.is_nullable)="YES","NULLABLE",null) AS mode
    ) as schema_col
FROM
  yourdataset.INFORMATION_SCHEMA.COLUMNS T
GROUP BY
  1,  2
) group by 1

answered Jul 30, 2021 at 5:39

Samuel

3,5581 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

schema of an existing table in JSON format using bigquery select statement in bigquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related