JSON database question.

[bkove12]

Hello all,

I just have a question regarding JSON and loading a big database file. I’m sure there is a simple way to do this it’s just going over my head. So basically I have a JSON database set up as follows;

{    "ID #": 1000,    "Sport": "MLB",    "Team": "sportsteam",    "Image": "1.jpg",    "Description": "blah blah"  }, {    "ID #": 1001,    "Sport": "MLB",    "Team": "sportsteam",    "Image": "2.jpg",    "Description": "blah blah 2"  }, {    "ID #": 1002,    "Sport": "MLB",    "Team": "sportsteam",    "Image": "3.jpg",    "Description": "blah blah 3"  },

I was wondering if there was a way I could call to a specific set of the data above using the ID # which would then call to the other parameters in that specific set, for images and text. So for example a user would tap a button that set the ID # to 1001 then it would take them to a page that shows the image and description that corresponds, so in this case 2.jpg and description “blah blah 2”. I just cant figure out how to single out one portion of the database to then use as an image or text. Any help would be amazing.

Thank you!

-B

[anaqim]

Hi,

If I get this correctly you wonder how to reference field inside the json?

It will also be easier if you do not use spaces in variables, like “ID #”…instead use just “id”

First you need to decode it to a table:

local json=require(“json”)

local myTable=json.decode(myJsonFile)

Then you can loop through the table to find what you need, for example (with fictive variables)

For i=1,#myTable do

      if myTable[i].id==1001 then

            myURL=myTable[i].image

      end

end

Hope it helps and sorry for posting unfinished post at first. This forum editor leaves a lot to be desired

[Michael_Flad]

As an alternative I’d like to suggest using Lua instead of Json (if you’re not forced to use Json) because that’s actually what Lua was originally designed for, and it’s quite good at it.

I.e. make your database like so

[lua]

local myData = {

    [1000] = {

        Sport = “MLB”,

        Team = “sportsteam”,

        Image = “1.jpg”,

        Description = “blah blah”

    },

    [1001] = {

        Sport = “MLB”,

        Team = “sportsteam”,

        Image = “2.jpg”,

        Description = “blah blah 2”

    },

    [1002] = {

        Sport = “MLB”,

        Team = “sportsteam”,

        Image = “3.jpg”,

        Description = “blah blah 3”

    },

}

– access is dead simple and lightning fast

local myEntry = myData[1001]

print( myEntry.Team )

[/lua]

You can put your database into a separate file and just use require or loadstring to load/use it.

[nick_sherman]

As long as the data isn’t too large, lua source tables work well. Once you reach a certain size just loading the table into memory can take a few seconds, and it makes sense to switch to SQLlite.

[bkove12]

Thank you so much for all of your suggestions. Just a quick follow up, I’m actually converting a CSV file into JSON because I thought that would be easier to work with within Corona. Would it be better for me to call to the CSV file and take data directly from there? If so how would I go about doing that so I could take individual data as stated above?

Thank you!!

[Develephant]

Hi,

My recommendation is to keep it as JSON. As noted above, if you are only handling a small amount of data then you can use a table. Otherwise you should create a sqlite database.

Assuming you have a JSON file called “data.json” formatted like so (I assumed your JSON example data is incomplete, as it is not valid JSON):

[{ "ID #": 1000, "Sport": "MLB", "Team": "sportsteam", "Image": "1.jpg", "Description": "blah blah" }, { "ID #": 1001, "Sport": "MLB", "Team": "sportsteam", "Image": "2.jpg", "Description": "blah blah 2" }, { "ID #": 1002, "Sport": "MLB", "Team": "sportsteam", "Image": "3.jpg", "Description": "blah blah 3" }]

You can load it, and convert it to a table type “database” using the following (as one option):

local json = require("json") local fd = io.open(system.pathForFile("data.json")) local data = fd:read('\*a') fd:close() data = json.decode(data) local db\_tbl = {} local record for i=1, #data do record = data[i] db\_tbl[record["ID #"]] = { sport = record.Sport, team = record.Team, image = record.Image, description = record.Description } end print(db\_tbl[1000].sport)

If you plan on using the data in multiple areas of your application, you may want to create a module to hold your data.

Hope that helps.

-dev

[bkove12]

Dev that worked great. Thank you and everyone else for your help!

[anaqim]

Hi,

If I get this correctly you wonder how to reference field inside the json?

It will also be easier if you do not use spaces in variables, like “ID #”…instead use just “id”

First you need to decode it to a table:

local json=require(“json”)

local myTable=json.decode(myJsonFile)

Then you can loop through the table to find what you need, for example (with fictive variables)

For i=1,#myTable do

      if myTable[i].id==1001 then

            myURL=myTable[i].image

      end

end

Hope it helps and sorry for posting unfinished post at first. This forum editor leaves a lot to be desired

[Michael_Flad]

As an alternative I’d like to suggest using Lua instead of Json (if you’re not forced to use Json) because that’s actually what Lua was originally designed for, and it’s quite good at it.

I.e. make your database like so

[lua]

local myData = {

    [1000] = {

        Sport = “MLB”,

        Team = “sportsteam”,

        Image = “1.jpg”,

        Description = “blah blah”

    },

    [1001] = {

        Sport = “MLB”,

        Team = “sportsteam”,

        Image = “2.jpg”,

        Description = “blah blah 2”

    },

    [1002] = {

        Sport = “MLB”,

        Team = “sportsteam”,

        Image = “3.jpg”,

        Description = “blah blah 3”

    },

}

– access is dead simple and lightning fast

local myEntry = myData[1001]

print( myEntry.Team )

[/lua]

You can put your database into a separate file and just use require or loadstring to load/use it.

[nick_sherman]

As long as the data isn’t too large, lua source tables work well. Once you reach a certain size just loading the table into memory can take a few seconds, and it makes sense to switch to SQLlite.

[bkove12]

Thank you so much for all of your suggestions. Just a quick follow up, I’m actually converting a CSV file into JSON because I thought that would be easier to work with within Corona. Would it be better for me to call to the CSV file and take data directly from there? If so how would I go about doing that so I could take individual data as stated above?

Thank you!!

[Develephant]

Hi,

My recommendation is to keep it as JSON. As noted above, if you are only handling a small amount of data then you can use a table. Otherwise you should create a sqlite database.

Assuming you have a JSON file called “data.json” formatted like so (I assumed your JSON example data is incomplete, as it is not valid JSON):

[{ "ID #": 1000, "Sport": "MLB", "Team": "sportsteam", "Image": "1.jpg", "Description": "blah blah" }, { "ID #": 1001, "Sport": "MLB", "Team": "sportsteam", "Image": "2.jpg", "Description": "blah blah 2" }, { "ID #": 1002, "Sport": "MLB", "Team": "sportsteam", "Image": "3.jpg", "Description": "blah blah 3" }]

You can load it, and convert it to a table type “database” using the following (as one option):

local json = require("json") local fd = io.open(system.pathForFile("data.json")) local data = fd:read('\*a') fd:close() data = json.decode(data) local db\_tbl = {} local record for i=1, #data do record = data[i] db\_tbl[record["ID #"]] = { sport = record.Sport, team = record.Team, image = record.Image, description = record.Description } end print(db\_tbl[1000].sport)

If you plan on using the data in multiple areas of your application, you may want to create a module to hold your data.

Hope that helps.

-dev

[bkove12]

Dev that worked great. Thank you and everyone else for your help!