Question

You are importing data as JSON into a new table. You run CREATE TABLE json_data ( city JSON ); and insert rows into this table. What is the correct syntax to see the list of cities?

a) SELECT * FROM json_data.city;
b) SELECT city FROM json_data;
c) SHOW COLUMNS FROM json_data LIKE 'city';
d) DESCRIBE json_data.city;

Answer 1

The correct syntax to see the list of cities from the json_data table is b) SELECT city FROM json_data; which will retrieve the entire JSON object from the 'city' column in the table.

Step-by-step explanation:

To retrieve a list of cities from a table named json_data with a column containing JSON data named city, the correct syntax would be option b) SELECT city FROM json_data; This SQL statement will select the entire JSON object stored in the city column for each row of the json_data table. If you wish to access a specific property within the JSON object, you would need to use JSON extraction functions specific to the SQL database you're using. However, for simply listing all city JSON objects, the provided syntax is sufficient.