Example: Construct JSON String using Select and Transform

Manifold's Select panel and Transform panel are often used together to choose a desired set of records and to then make a change to those records.     We can, of course, make such changes using SQL queries as well.   However, many times it can be easier to make changes interactively using techniques like those shown in this topic.   Even if our SQL skills are very strong we might be able to make changes more quickly through interactive dialogs than constructing an elegant query to do the same.  The Transform panell also provides a "live" preview of proposed changes so we can use them to spot mistakes before they happen.

 

In this example we will create a new text field that will be used for a JSON string that will encode information within the table in a form that is portable to any other application that can use JSON.   JSON (JavaScript Object Notation) is the stylish, lightweight data-interchange format of the day.

 

Our starting table uses data taken from a database of Carolingian coin hordes discovered in Europe.  Coin hordes are usually small groups of coins buried in centuries past, usually for safekeeping, and then never retrieved by their owners.  Most coin hordes do not have precious coins but because the coins can be dated precisely they serve many useful functions for archaeologists.

 

Our table contains a field called Town where the coin horde was found and two fields called NonCoin and Photo which indicate whether archaeological items that were not coins also were found in the horde and whether a photo is available of the find.   All fields in the table are nvarchar types, a typical situation in data published online.  Most publication formats do not have more sophisticated types such as boolean types to indicate a yes or no situation like whether a photo is available or not.

 

In this data set the yes or no status for photos and non-coin finds were represented simply by the letter Y if there is a photo available and also the letter Y if items in addition to coins were found, with no comment otherwise.   The table therefore just has a Y character representing a "yes" and a NULL representing a "no."   That is not a clean way of representing such a boolean status so when we create the JSON string representing that same information we will do so by using genuine boolean values, true and false, within the JSON string.

 

Our first step is to add an nvarchar field for the JSON string.

 

eg_construct_json01_01.png

 

We choose Edit - Schema to launch the Schema dialog, which allows us to modify the table's structure.

 

eg_construct_json01_02.png

 

In the Schema dialog we click  <new field> and then in the Field box we provide the name JSON_info for the new field.  In the Type box we choose nvarchar as the data type for the field.  We press Add to add the new field to the schema, and then we press OK to close the Schema dialog.  

 

eg_construct_json01_03.png

 

That creates a new field called JSON_info in the table.   In the illustration above we have adjusted column widths to provide more space for the JSON_info field, which will contain long text strings.

 

The new field is initially loaded with NULL values for each record.   Our task is to construct a JSON string that encodes the Town name, which we will call a "Site," plus the boolean status of the NonCoin and Photo fields.   An example of the correct JSON string for the first record would be:

 

{ "Site": "Krinkberg", "NonCoin": true, "Photo": false }

 

As we can see, JSON is fairly obvious in how it encodes information.   Strings are enclosed in double " quotes and values such as numeric values or boolean values are not quoted.

 

To begin we click the Contents pane tab and then choose the Transform panel in the pane's pull-down menu of panels.  

 

We choose the new JSON_Info field as the target field in the pull-down menu of fields at the top of the panel.   The template we choose will operate on that field.

 

eg_construct_json01_04.png

 

We choose the Concatenate transform template.   Within the source combo box, the upper one, we can put whatever we want as the starter value to which the Concatenate value will be added.   We will start with, enclosed within single ' quote characters so it can be digested by the Manifold query engine that powers the panel, the text value

 

'{ "Site": "'

 

That is simply the first few characters that will begin the JSON string for each record.    Concatenated to that we want the value of  the Town field for each record.  When we enter the last single quote ' character above and choose the Town field in the lower box Manifold will preview what will happen in the table.  

 

eg_construct_json01_03a.png

 

If the preview looks wrong we can see what error we have made and correct it.

 

We press Update Field to apply the template.

 

eg_construct_json01_05.png

 

The result is that the JSON_info field is now populated with the left curly bracket { character that starts a JSON string, the name of the first entity in double quotes, a colon : character and then a double quote " character and the name of the town.  

 

If we wanted, we could use the Transform panel again to concatenate a double quote " character and a comma , character to the end of the string we are building to "close off" the entity specification for Site.   However, it is just as easy to do that in the next step as part of continuing to build the string to add the NonCoin entity.  

 

To begin adding the NonCoin values we will first select all records where NonCoin is Y.   We use the  Select panel.

 

eg_construct_json01_06.png

 

We choose the Text Contains template, we choose NonCoin as the source field and 'Y' as the Search for value.   

 

As with almost everything in Manifold we are talking to the SQL engine through this panel so we use SQL syntax, for example, referring to literal values within single quote ' characters.  As we enter those values Manifold will preview the selection in the window, a useful way to check that what we are doing is correct.   Press Replace Selection.

 

eg_construct_json01_07.png

 

All records where NonCoin is Y are now indicated in red selection color.   

 

Important:  In the next step we will use the Transform panel with the Restrict to selection box checked.  

 

eg_construct_json01_09.png

 

This ensures the transform will apply only to those selected records.   Those are the records for which we want to add a "NonCoin": true, specification to the JSON string.  This is the key to how this example works so we must make sure to check the Restrict to selection box.

 

We click on the Transform panel again, making sure to first check the Restrict to selection box.

 

eg_construct_json01_08.png

 

As before we choose the JSON_info field as the target field at the top of the panel.   But now we also choose the JSON_info field as the source field, and to that we will Concatenate the value

 

'", "NonCoin": true,'

 

This has the effect of first closing the double quote in the string and then adding a specification for the NonCoin entity as true.   We can see if the preview has the desired effect and then after checking that it does, we press Update Field and then Close.

 

tech_angus_sm.png

Tech Tip:  Manifold will start previewing as soon as it has an expression that can be parsed as a valid expression.  If we are not choosing a field but specifying a literal string expression in one of the combo boxes that expression will not be a valid expression until the final single quote ' character is entered.    We can achieve a "live," on-the-fly preview of what we enter using a small trick:

 

eg_construct_json01_23.png

 

Begin by entering an empty string, just two single quote ' characters next to each other, or two single quote ' characters with a space character between, into the Concatenate box.  That empty value or just a space will be concatenated onto what is already there.   If we then keyboard our desired expression for NonCoin into the box in between those two single quote ' characters Manifold can preview it as we keyboard it, character by characters.   We can see what we are keyboarding previewed in "real time" in the table.    That is a very cool and useful effect and a great way to avoid typos and other mistakes.

 

After we press Update Field we see that the desired additional text has been concatenated onto the existing JSON string but only for those records that were selected.   All of the selected records, that is, all of the records we selected for NonCoin being Y, now have their JSON strings extended so that the NonCoin entity in the JSON string has a boolean value of true.

 

eg_construct_json01_10.png

 

We will now preform an analogous operation on all the records where NonCoin has a NULL value.    To do that we just invert the selection.

 

With the focus on the table, choose Edit - Select Inverse or press Ctrl-I to invert the selection.

 

eg_construct_json01_11.png

 

The selection is instantly inverted so that now all records where NonCoin has a NULL value are selected.

 

eg_construct_json01_12.png

 

Once again we use the Transform panel.

 

eg_construct_json01_09.png

 

Once again we check the Restrict to selection box.

 

eg_construct_json01_13.png

 

As before we choose the JSON_info field as the target field at the top of the panel and we also choose the JSON_info field as the source field.   To that we will Concatenate the value

 

'", "NonCoin": false,'

 

This has the effect of first closing the double quote in the string and then adding a specification for the NonCoin entity as false.   We can see if the preview has the desired effect and then after checking that it does, we press Update Field.

 

The result in the table is that all the selected records, those for which NonCoin is NULL, now have their JSON strings extended with a NonCoin boolean value of false.

eg_construct_json01_14.png

In what we have done so far we have shown the essence of this example.   

 

Let us continue this example by basically repeating what we did for the NonCoin field but now for the Photo field.   We begin by selecting all records for which Photo has a Y value.   We use the Select panel.

 

eg_construct_json01_15.png

 

In the panel we choose the Text Contains template.  We choose Photo as the field of interest and 'Y' as the Search for text.  Manifold previews the selection in the table and, once we check the preview is what we want we press the Replace Selection button.

 

eg_construct_json01_16.png

We now have selected all records for which Photo is Y.    

 

Our next step is to use the Transform panel Concatenate template to add to the JSON string.

 

eg_construct_json01_09.png

 

As before, we make sure to check the Restrict to selection box.

 

eg_construct_json01_17.png

 

We choose the JSON_info field as the target field at the top of the panel and we also choose the JSON_info field as the source field.   To that we will Concatenate the value

 

'"Photo": true }'

 

This has the effect of adding a specification for the Photo entity as true and then closing the JSON string with a curly bracket } character.   We confirm the preview has the desired effect and then after checking that it does, we press Update Field and then Close.

 

The result in the table is that all the selected records, those for which Photo is Y, now have their JSON strings finished with a Photo boolean value of true.

 

eg_construct_json01_18.png

 

To finish the JSON string in an analogous way for those records where Photo is NULL we choose Edit - Select Inverse or press Ctrl-I to invert the selection.

 

 

eg_construct_json01_11.png

 

 

That inverts the selection.   Now all records are selected where Photo is NULL.

 

eg_construct_json01_19.png

 

Once again we use the Transform panel.

 

eg_construct_json01_09.png

 

We make sure the Restrict the selection box is checked in the Transform panel.

 

eg_construct_json01_20.png

 

Once again the JSON_info field is our target and the JSON_info field is the source field.   To that we will Concatenate the value

 

'"Photo": false }'

 

This will add a specification for the Photo entity as false and then close the JSON string with a curly bracket } character.   We confirm the preview has the desired effect and then we press Update Field.

 

The result in the table is that all the selected records, those for which Photo is NULL, now have their JSON strings finished with a Photo boolean value of false.

 

eg_construct_json01_21.png

 

We choose Edit - Select None to deselect all records so we can admire the final result.

 

eg_construct_json01_22.png

 

After thinking for a few moments, "Wow.  Cool." we begin to realize that perhaps we made a small error.    We've named the entity that gives the name of the hoard as Site in the JSON string but in the table the field is called Town.   That is not a big deal but it could be that people who are familiar with this data set may expect the place name to be called Town.  They might expect our JSON string to use the same name.    

 

To avoid any worries we will change Site to Town in the JSON string.   That is easy to do with the Transform panel.

 

eg_construct_json01_09a.png

 

Important: First, we un-check the Restrict to selection box.

 

eg_construct_json01_24.png

 

We will use the Replace Text, All template with JSON_info as both the target field at the top of the panel and also the source field.   We will Search for the value 'Site' and Replace with the value 'Town'.  

 

eg_construct_json01_25.png

 

As soon as we construct a comprehensible expression in the Replace with box the system will preview what it will do in blue preview color in the table.   This is a truly wonderful feature that helps us avoid errors.

 

eg_construct_json01_26.png

 

If we like the preview, we press the Update Field button.

 

We now have exactly the table we want, including a JSON string that can be consumed by other applications which correctly gives the value of Town as strings.    Better than a text field that sometimes contains the letter Y and sometimes a NULL, the JSON string now gives the values of NonCoin and Photo explicitly as boolean values of true or false.   Explicit values of either true or false are safer to use since many applications cannot handle NULL values correctly.

Notes

Widely Applicable Work Flow - The heart of this example is to use the Select panel to choose those records on which we want to operate, to use the Transform panel to concatenate the text desired for selected records, and then to change the selection so we can make analogous changes to other records.   

 

We can use that work flow as much as we want to make whatever changes we want using any of the templates that serve our purpose.   In this example we built a string field through concatenation, but we could in other situations use similar work flow to edit string fields, to copy data between fields, to modify numbers or other values and so on.

 

SQL in Combo Boxes - This example uses either the names of fields or simple literal expressions in the combo boxes when using the Concatenate template.   If we wanted to, we could use SQL expressions, including functions, in those combo boxes as a combination between using the dialog alone or writing an SQL query to do what we want.

 

Suppose for example that we have built up the JSON string as follows, using the first record as an example:

 

{ "Site": "Krinkberg", "NonCoin":

 

We are now ready to add a true or a false value for the NonCoin entity.   In the example we did something similar by first selecting all records where NonCoin was Y, using the Transform dialog to concatenate a true, inverting the selection and then using the Transform dialog to concatenate a false.

 

But we could accomplish all of that in one step by using JSON_info as the source and the target field and in the Concatenate box entering a single SQL expression:

 

CASE WHEN NonCoin IS NULL THEN 'false' ELSE 'true' END

 

For all records the CASE construction uses the Manifold SQL function IS NULL to see if NonCoin is NULL.  If NonCoin is NULL, then the string literal false gets concatenated and if it is not NULL then the string literal true gets concatenated.

 

It is true that someone who is a real SQL wizard (and fanatic) may prefer to use the Command Window to write masterful SQL all at one go.  But sometimes it is very convenient, especially with a preview of what will happen at hand, to simply plug a bit of SQL into a combo box to do in one step what otherwise might take several steps involving selection and transforms.

 

The Edit Query button - At any time we can press the Edit Query button to see what Manifold uses as an SQL query to implement what we have commanded in the dialog using the chosen template and the specified combo box values and options.  That can be instructive to see how selections are used and how queries can be written.

 

For example, consider the last step of building the JSON string in the example above, where we had all records selected for which Photo was NULL, and, with the Restrict to Selection box checked, we concatenated the value:

 

'"Photo": false }'

 

If at that moment instead of pressing the Update Field button we had pressed the Edit Query button the Command Window would have opened up loaded with the following query:

 

-- $manifold$

--

-- Auto-generated

-- Transform - Concatenate - Update Field

--

UPDATE [Coin Hordes]

SET [JSON_info] = [JSON_info] & '"Photo": false }'

WHERE ([mfd_id] IN CALL SelectionKeys([Coin Hordes], 1)) XOR SelectionIsInverted(1);

 

That is the query which appends the desired string to the existing JSON_info field, but only for those records that are selected.  

 

The guts of the query are simple, a trivial use of the concatenation & operator.   If we are learning SQL taking a look at queries like this can teach a lot, much of which is encouraging along the lines of "Wow! The basics really are simple."   But at the same time if we want to take advantage of infrastructure like Selection, that can be easier to do in the Transform dialog than to expend the extra labor entering all that text which comes after WHERE in the query above.

Coin Hordes

Coin hordes (or "hoards" in American English) are groups of coins that were buried or hidden in the past and then never retrieved by their owners.   When discovered in modern times they provide a time capsule, a marker, that can help date the vicinity in which they are found.  

 

Throughout history coins often have been made of durable metals, such as bronze, that have endured the centuries in recognizable form.  Coins can often be dated to within a few years given the tendency of new rulers to mint new series of coins, so the discovery of a coin horde can pin the date of the location to within a few years.   

 

Some coin hordes contain precious metal coins but most are petty coins buried on the eve of battle by soldiers or secreted by common people.  Their great value today lies in their historical value as markers of time and place.

 

eg_construct_json01_27.png

 

The illustration above shows coins, Christiana religio deniers, of Louis the Pious from the Häljarp, Sweden, coin horde discovered in 1905, the fifth row in our example table. The most recent coin in the horde dates from 840 AD.   The obverse bears a cross surrounded by the Latin legend Hludovvicus imperator or "Emperor Louis", while the reverse bears a temple with a cross within surrounded by the Latin legend Christiana religio or "Christian religion".

 

Citation - The example data set of Carolingian Hoards used in this documentation was downloaded from dataverse.harvard.edu and is cited as Coupland, Simon; Maione-Downing, Brendan, 2013, "Geodatabase of Carolingian Coin Hoards: AD 751-987 (Version 1.1)", doi:10.7910/DVN/23984, Harvard Dataverse, V1  

 

See Also

Selection

 

Contents Pane

 

Contents - Select

 

Contents - Transform

 

Transform Templates

 

Transform Templates - Text

 

Example: Select a Range of Records in a Table - We can select or deselect a range of records between any two records in a table window using fast and easy mouse commands.

 

Example: Mouse Selection in a Drawing Window - Using the mouse to select objects in a drawing window.   This could be a drawing layer in a map or simply in a drawing window.

 

Example: Transform Field Values using an Expression in the Transform Panel - How the Expressions tab of the Transform panel may be used to change the values of fields.  

 

Example: Edit a Drawing with Transform Templates - In this example we open a drawing and edit objects in the drawing using Transform panel templates.  Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the  Transform panel to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.