Appropriate use of JSON?

General
1

I’ve never used - or even looked at - JSON before but might want to now, so I’m trying to determine whether what I want to do is a good use of JSON.

I want to add another listener to a small server app I have. This would listen for requests from a couple of apps I have, for AddressBook information, thus allowing a single AddressBook database to serve a small group of people. Initially I just assumed I’d have the usual SQLite database schema (firstname, lastname, etct etc), read out a record, convert it to a JSON object, stringify that and send to the requester. Then it struck me that perhaps it makes more sense to just have the JSON object in the database as-is, saving some steps. So the schema would simplify down to:

CREATE TABLE addressbook
  {
  id INTEGER PRIMARY KEY,
  addressbookentry TEXT
  }

or perhaps BLOB rather than TEXT.

I couldn’t find any XOJO Blog entries about JSON,and the JSON examples in the IDE are clear but quite simple.

So I think at this point I’m looking for validation or otherwise of this approach, thanks.

2

JSON is kinda the default and expected format when talking with all kinds of servers (sending and receiving information). So I’m going to say, based on your needs it is exactly what you need. Please see this project Year of Code 2025: March Project, Web Apps – Xojo Programming Blog it includes code to work with JSON files. Maybe it will help.

Additional info: Create Fast RESTful APIs in Xojo – Xojo Programming Blog

3

I think I’d rather go with a “normal” table and return the data like this:

SELECT json_object(
  'id', id,
  'name', name,
  'email', email
) AS json_data
FROM users
WHERE id = 1;
3 Likes
4

I agree with Max. I would not store my data as JSON as that then limits what you can do with SQL significantly and may be a stumbling block later (if it isn’t already). It’s quite trivial to build methods to convert JSONItems for storage in tables.

JSONItem is easy to work with and shares a lot of the same API as Dictionary. Don’t create more work later for a (ill-advised) shortcut now.

In SQLite, at least, a lot of the work can actually be done for you as there’s support for JSON objects built in, which is likely to be very efficient.

3 Likes
5

Thanks, I’ll take a look at the Year of Code projects. Also yes I saw that SQLite has a large set of JSON functions; not written any code yet, just did a quick check that the SQLIte buillt-into the IDE includes them (I would have expected it to but always worth a check).

6

JSON is very good for working.
For you problem, it is not a good idea beconse you can’t modifie JSON
It is difficile do a search.
I think il is preferable to save the source.
Also it is very simple to create a JSON text.

I use JSON to save many think on the drive (Properties, Picture, LisBox … ).

7

OK, let me broaden the question a bit. One of the issues with a “traditional” database schema, it seems to me, is that for some fields, multiple values are required. “Address” is a typical example, and so one sees multiple columns (address1, address2, etc) being declared, just in case, and some of these will never be used. What about a database schema where, instead, one just declares “address”, or “phone”, or “email”, and then puts a JSON array in that field, which may then contain as many or as few items as are actually required? The database schema is thus kept simpler and is less likely to need updating (a process which is possible but leads to forwards and backwards compatibility issues, potentially).

(The above pre-supposes that my understanding of what is possible with JSON is correct).

8

Let me put it this way:
If there’s a chance that you’ll ever need to run a query on your database table for the value, you’re better off making that its own field rather than serializing data (such as using JSON as the field value).

Now, there are appropriate times to store data in a serialized format, and its up to you to decide when that is, but it’s really just easier to design your database and application to use separate fields when you know what data you’re storing from the start than it is to go back later and modify both then write a conversion to unserialize the data you then need extracted as a separate field on the table.

Like I said before: don’t take a shortcut now that may cause you headaches later. It’s just easier to design for the correct fields you need from the start.

1 Like
9

Let me give an example directly related to the values you’re asking about.

Let’s say you build the system the way you’re proposing and shove all of the address data in to a JSON object and store that. Now, at some point down the road, you decide that you want to be able to view all records for a specific country or state/provice. You’ve created more work for yourself by storing the data in JSON format. Sure, you can still use the built-in JSON functionality of the database but you’re sacrificing efficiency. You know from the start what fields comprise an address, so why would you negate the benefits by just storing it all as serialized data?

10

I think there, one would have to carefully consider the address components. The fields might then be

Address (such as house name, street name, village name)
PostTown
PostCode
County
Country/State etc

In this example, for our house all three address components would be required, if one actually lived in the PostTown at a numbered house then there would be just the one.

11

This is pretty much standard in database design as it’s such a common bit of data to store. It’s typically:

  • Address Line 1 (1234 Some St)
  • Address Line 2 (C/O Some Dude)
  • City (New York)
  • State/Province (New York)
  • Country (USA)
  • Postal Code (10001)

Now, if you want to support multiple entries for each user, you could have another table that just stores the addresses then use the ID of the person/company from the main table as a foreign key on the address table to link things together. This would also give you the ability to have a field for the address type (such as Home, Work, Shipping, Billing, etc) and make it easier to return the specific necessary address for things.

One common implementation I see (and, admittedly, use myself from time-to-time) is to have a “meta” table where you store key/value pairs associated with primary records via foreign keys. So I may have a user_meta table with data keys like address1_line1, address2_line1, address1_type. This means that one can easily add or remove associated data later without modifying table design, but it can make implementation a bit trickier in some scenarios.

That might be defined as:

CREATE TABLE "user" ("id" INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "email_address" TEXT NOT NULL UNIQUE)

CREATE TABLE "user_meta" ("id" INTEGER PRIMARY KEY UNIQUE, "user" INTEGER NOT NULL REFERENCES "user" ("id"), "key" TEXT NOT NULL, "column4" TEXT)

The goal of all of this is to lean on the database management system (DBMS) for processing as much as possible via SQL as the DBMS is going to be more efficient than your code at collating, sorting, and associating data.

1 Like
12

Stockage with JSON:
{
“adresse_01”: “Appartement 7”,
“adresse_02”: “66 rue Mozart”
}

Stockage in texte:
Appartement 7
66 rue Mozart

I think the text format is more interesting.

13

This really screams for a separate table to hold addresses. Not separate fields and definitely not json.

1 Like
14

Anything where there might be multiples, then, such as addresses, emails, phone numbers.

15

Yes. Exactly.

16

Yes they should all be separate tables.

Now if you had a contact entity where there was only ever one phone number or email, a contacts table would be sufficient. But still a subsidiary table, keyed to the main table’s ID.