Example: Construct JSON String using Select and Transform

Manifold's Select dialog and  Transform dialog 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.  Dialogs also provide 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 click on the new field asterisk * column head.

 

 

eg_construct_json01_02.png

 

 

In the New Field dialog we provide the name JSON_info for the new field and choose nvarchar as the data type for the field.  We press Add Field to add it to the table.  That creates a new field called JSON_info.

 

eg_construct_json01_03.png

The 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 choose Edit - Transform to launch the Transform dialog.   The target field chosen at the top of the dialog, upon which the dialog operates, will be the new JSON_Info 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 dialog, 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.  If the preview looks wrong we can see what error we have made and correct it.

 

We press Update Field to apply the template, and then we press Close to close the dialog.

 

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 : characer and then a double quote " character and the name of the town.  

eg_construct_json01_05.png

tech_angus_sm.png

Tech Tip:   We do not have to Close the Transform dialog in between the steps shown in this example.  The moment we press Update Field the template makes changes to the table.    We could then go on to make the next change desired, press Update Field again and so on.    

 

The problem with that is that because Manifold shows a preview of what the dialog is about to do it is easy to forget whether what we are looking at is the preview or the result of pressing the Update Field button.  There is no "undo" for such global changes so if we are not sure that we've pressed the Update Field button we should not risk pressing it a second time and thus "doubling up" the changes.   So it is safer, especially for beginners, to just Close the dialog after each step.

 

If we wanted, we could run the Transform dialog 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 choose Edit - Select to launch the Select dialog.

 

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 dialog 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 and then Close.

 

eg_construct_json01_07.png

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

 

eg_construct_json01_09.png

 

Important:  We will use the Transform dialog with the Restrict to selection box checked.  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 heart of how this example works so we must make sure to check the Restrict to selection box.

 

We launch the Transform dialog 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 dialog.   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.

 

Choose Edit - Select Inverse.

 

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 choose Edit - Transform to launch the Transform dialog.  

 

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 dialog 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 and then Close.

 

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 choose Edit - Select to launch the Select dialog.

 

eg_construct_json01_15.png

 

In the dialog 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 and then Close to close the dialog.

 

eg_construct_json01_16.png

We now have selected all records for which Photo is Y.     Our next step is to choose Edit - Transform to launch the Transform dialog so we can once again use the 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 dialog 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.

 

 

eg_construct_json01_11.png

 

 

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

eg_construct_json01_19.png

 

We choose Edit - Transform to launch the Transform dialog.

 

eg_construct_json01_09.png

 

Right away, before doing anything else, we check the Restrict the selection box

 

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 and then Close.

 

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 dialog.

 

 

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 dialog and also the source field.   We will Search for the value 'Site' and Replace with the value 'Town'.    Press Update Field and then Close and we are done.

 

eg_construct_json01_25.png

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 and, more sensibly than sometimes a text value of Y and sometimes as a NULL, 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

Same Work Flow - The heart of this example is to use the Select dialog to choose those records on which we want to operate, to use the Transform toolbar 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 are building 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 will likely 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.

 

 

See Also

Selection

 

Select Dialog

 

Transform Dialog

 

Transform Templates

 

Transform Templates - Text

 

Example: Using a Selection Box in a Table - In tables we can use the mouse to select records by drawing a selection box.  Ctrl-click and drag anywhere in the table to select all records with any part in the selection box.   Using the Alt key makes that a deselect: Alt-Ctrl click and drag anywhere in the table to deselect records with any part of the record in the deselection box.

 

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: Combining Selections using the Select Dialog - How to use selections to solve a one-off problem casually that would be more difficult to solve rigorously with SQL.   The objective in the example's scenario is to visit a point of tourist interest while travelling.    We will use mouse selection as well as the Select dialog.

 

SQL Example: Using Selection Query Functions - How to manipulate selections within a query.

 

Example: Two Drawings from the Same Table - Take a table with a geom field that is visualized by a drawing.  Add a second geom field to the table and create an rtree index on that field so it can be visualized by a drawing.   Copy the first drawing, paste it and adjust the pasted copy so it uses the second geom field. Demonstrate how to use the Transform dialog to show "live" modifications in the second drawing compared to the first drawing.

 

Example: Copy one Column into Another Column with Transform - How to use the Transform dialog to copy the contents of one column in a table into another column, but only for selected records.  Uses the Products table from the Nwind example data set.  

 

Example: Transform Field Values using an Expression in the Transform Dialog -  How the Expressions tab of the Transform Dialog may be used to change the values of fields.   We include an example of changing the price of selected products and using two different Transform dialogs open at the same time for two different table windows.

 

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

 

Example: Use a Transform Dialog Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform Dialog 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.

 

Example: Clip Areas with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Smooth Lines with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to make lines smoother in a drawing so that longer lines are smoothed more.  Includes examples of using the Edit Query button to show how different queries are created automatically depending on if we want to update a field or to add a new component.

 

Example: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.