JSON Servers

JSON format files are text files written in JavaScript Object Notation (JSON).   JSON has become popular for interchange of tabular data and metadata.   Manifold can import and link JSON files.  Manifold can also export tables to JSON files.  In addition to working directly with JSON files, Manifold's JSON server dataport can connect to data provided by web servers in JSON format.   This topic shows how.   A related topic, JSON, shows how to work with local files in JSON format.

 

Please read the JSON topic for important information about JSON format and Manifold.  See also the CSV Servers file for tips that apply to using CSV servers, GeoJSON servers, and JSON Servers.

 

We can bring data from JSON servers into Manifold using two, related techniques:

 

 

 

 

Connecting to a JSON server:

 

  1. Choose File - Create - New Data Source in the main menu, or right-click in the Project pane and choose New Data Source.

  2. In the dropdown menu choose More... to launch the New Data Source dialog.

  3. Choose Web Server: jsonserver in the Type box.

  4. Enter the URL for the JSON data in the Source box.

  5. Default settings for other options will usually work for most JSON data.   Press Create Data Source.

  6. A new data source appears in the project.  Open it to see the table that is linked in from the JSON file.

 

Creating a local copy of the JSON table:

 

  1. Click the table linked in from the JSON to highlight it.

  2. Press Ctrl-C or click the Copy button in the Project pane toolbar.

  3. Click anywhere in the main, Manifold .map part of the project outside of the new data source's hierarchy.

  4. Press Ctrl-V or click the Paste button in the Project pane toolbar.

 

Tables that are linked from JSON data are neither selectable nor editable since they do not have any indexes.  We cannot add an index to a linked JSON table since JSON format does not support indexes.   We can, however, add a key field and index to a local table that has been copied and pasted from a linked JSON table.  Such local tables are fully capable, native Manifold tables.   We can easily add a key field and index to them to enable editing and selection in the table, using the procedure given in the Add an Index to a Table topic.

 

Adding an index to a table:

 

  1. Double-click on the table to open it.

  2. Choose Edit - Schema.

  3. In the Schema dialog click the Add Identity button.

  4. Press the Save Changes button.

 

New Data Source Dialog and Controls

In the main menu, choose File - Create - New Data Source.   The dropdown menu provides a list of favorites to choose from as well as a More... option.  

 

 

Choose More... to launch the New Data Source dialog.  Choose Web Server: jsonserve in the Type box.

 

 

Name

Name for the new data source, "Data Source" by default.  Specify a more memorable name as desired.  If we forget the origin of a data source we can hover the mouse over the data source and a tool tip will provide connection information.

Type

Choose Web Server: jsonserver in the Type box to connect to a JSON format source over the web.  Choosing the JSON server type will use the JSON server dataport regardless of the file extension, either .json, or .geojson, or other extension.

Source

A connection string to the web server JSON data. This may be a simple URL as in the example above, which is just a URL to a file on the server, or a very lengthy, complex  URL/connection string that embeds parameters such as keys that grant access or other parameters.  The connection string can also be entered using the Web Login dialog launched by the [...] browse button.

 Browse button

Click to launch the Web Login dialog, to allow use of a login and password plus use of a proxy server if desired.   The Web Login dialog is also handy for providing a Test button that can be used to test the connection.

Open as read-only

Open the data source read-only.  Has no effect with JSON servers since they are read-only in any event.

Cache data

Cache data downloaded from the server while the project is open.  Cached data is saved within the .map project itself in a Cache sub-folder in the System Data hierarchy. Also provides greater flexibility with read-only data sources.

Save cached data between sessions

Save the cached data for the next time this project is opened,  within the .map project itself in a Cache sub-folder in the System Data hierarchy.   Caution: checking this box can result in very large .map files when the results of browsing very large data from web servers are all saved.  However, having such data cached in the .map is handy for offline browsing of the project.

Create Data Source

Create the new data source in the project pane and close the dialog.

Edit Query

Launch the Command Window loaded with a query that creates the data source using the given settings.  A great way to learn how to use SQL to create data sources.

Cancel

 Exit the dialog without doing anything.

 

 

 Pressing the browse button next to the Source box launches the Web Login dialog, to specify a server and connection characteristics.

 

 

Server

The connection string for the server.  This may be a simple URL as in the example above, which is just a URL to a file on the server, or a very lengthy, complex  URL/connection string that embeds parameters such as keys that grant access or other parameters

Use login and password

Check this box for servers that require logging in with a login name and a password, providing the required name and password in the Login and Password boxes.

Use proxy server

Check this box when connecting through a proxy server.   The Proxy, Login, and Password boxes allow specifying the connection string to the proxy server as well as the login name and password required to use the proxy server.

User agent

Identifies what application (Manifold) is asking for a connection.  Some web servers want to know what client software is connecting, for compatibility or for business reasons.  The default string optimizes compatibility (Mozilla is very generic) while also identifying Manifold Release 9 as the client.  Users can adjust the string as necessary to comply with any special server requirements.

API key

Provide a key that authorizes use of an API when connecting to a proprietary data source that requires such a key.  Not used with JSON servers.

Application key

A secondary application key or authentication code for those servers, such as here.com (also known as wego.here.com) that may require it.   Not used with JSON servers.

Timeout

Specify a time in milliseconds to wait for connecting to the specified server.  Use 0 for the default timeout or specify whatever is the desired time to wait before giving up on the server.

Test

Press the Test button to try the connection using the specify parameters.   If successful, a Connection established information dialog will pop open.

 

Example: Connect to a JSON File through the Web

Web servers providing JSON data may create the data on the fly or they may simply serve a JSON file stored on the server.  A very common use of JSON servers is to access data published as JSON files and placed on web server for each access by anyone.    

 

For example, if we have a website called www.manifold.net and we want to make a JSON file available to Manifold users through the JSON server dataport, all we need do is to place that file somewhere on our site where it can be accessed with a URL.   Suppose we have a file called customers.json that provides a list of customers and their email addresses.  This is the same example file used in the JSON topic.  We could put it into a folder in our site called ~/files and the URL to connect to it would be:

 

http://www.manifold.net/files/customers.json

 

The link above is a live link, so we should not just click it if we do not want a list of 1000 customers (synthetic test data, not real customers) to appear in our browser.    To bring the data into Manifold. we can use that link to create a new data source.

 

We choose File - Create - New Data Source.  

 

 

Choose More... to launch the New Data Source dialog.

 

 

We use Data Source  as the Name.  We choose Web Server: jsonserver as the Type.   In the Source box we enter the URL connection string:

 

http://www.manifold.net/files/customers.json

 

We uncheck Save cached data between sessions and leave other options at their default settings.  Press Create Data Source.

 

 

A new data source appears in the project pane.  We expand it to see it contains a table called customers.     We will not see the table until data comes down from the server, which can take seconds, or even tens of seconds, with slower connections or slower servers.   JSON is also a very slow format, because it is plain text, so larger JSON tables can take seemingly forever to fetch from servers.

 

 

We double-click open the table and see it contains the same table as imported in the example in the JSON topic, with gray background used on all fields to show the table is read-only.  The table is read-only, since it is still resident in the JSON file up in the manifold.net server.   However, as a Data Source with the data on the server, if there are any changes in the JSON file placed on the manifold.net website, for example, if there were edits to the table in the file, the table in the data source in our project would be updated with fresh data from the server when we refresh it.   

 

 

As convenient as it may be to leave the data on the server, in most cases, a better way to use the data is to click the table in the data source, press Ctrl-C for Copy, and then click into the main part of the project (outside the Data Source) and press Ctrl-V for Paste.   That copies the table into the project.   We can then open the table, add an index, and the table will be read / write.  We can then do with it whatever we want.

 

See Also

Schema

 

File - Create - New Data Source

 

CSV Servers

 

GeoJSON

 

GeoJSON Servers

 

JSON

 

Example: Import CSV and Create a Drawing - Import a CSV file by creating a New Data Source, copying and pasting the table conveyed by the CSV, prepping the table for use as a geocoded table and then creating a drawing.  

 

Example: Create a Drawing Dynamically from a Geocoded Table - A more general, cooler way to create a drawing from a geocoded table, without adding any fields to the table.   We create a small query that generates geometry on the fly, and then we create a drawing from the query.   This is how more advanced users often do it, using a technique that is perfect for creating drawings from tables in remote DBMS packages or read-only files.