B4J Code Snippet [B4X] B4XTable Export to JSON

Hi, how can i export or convert the B4XTable rows to a json file?

 

[Erel]

Depends on JSON library.

Public Sub ExportTableToJSON (Table As B4XTable, Dir As String, FileName As String)
    Dim data As List
    data.Initialize
    Dim rs As ResultSet = Table.sql1.ExecQuery("SELECT * FROM data")
    Do While rs.NextRow
        Dim row As List
        row.Initialize
        For i = 0 To Table.Columns.Size - 1
            Dim c As B4XTableColumn = Table.Columns.Get(i)
            Select c.ColumnType
                Case Table.COLUMN_TYPE_DATE
                    row.Add(rs.GetLong(c.SQLID))
                Case Table.COLUMN_TYPE_NUMBERS
                    row.Add(rs.GetDouble(c.SQLID))
                Case Table.COLUMN_TYPE_TEXT
                    row.Add(rs.GetString(c.SQLID))
            End Select
        Next
        data.Add(row)
    Loop
    rs.Close
    Dim Headers As List
    Headers.Initialize
    For Each c As B4XTableColumn In Table.Columns
        If c.ColumnType = Table.COLUMN_TYPE_VOID Then Continue
        Headers.Add(c.Id)
    Next
    Dim jg As JSONGenerator
    jg.Initialize(CreateMap("Columns": Headers, "Data": data))
    File.WriteString(Dir, FileName, jg.ToPrettyString(4))
End Sub

The output looks like:

{
    "Columns": [
        "County",
        "Name",
        "State",
        "Random Number"
    ],
    "Data": [
        [
            2013,
            "Aleutians East Borough, AK",
            "AK",
            11.42104614
        ],
        [
            2016,
            "Aleutians West Census Area, AK",
            "AK",
            49.0616097
        ],
      
        [
            56045,
            "Weston County, WY",
            "WY",
            -36.08093654
        ]
    ]
}

 

[Brian Michael]

Hi Erel, thanks for answer.

I need the output looks like:

    "Data": [
        {
            "Country":"2013",
            "Name":"Aleutians East Borough, AK",
            "State":"AK",
            "Random Number":"11.42104614"
        }
    ]
}

Its there a way to do it?

 

[Erel]

Of course there is a way and it should be quite simple to do based on the code I posted. I recommend you to watch the video tutorials about collections and how to work with maps: https://www.b4x.com/etp.html

 

[Brian Michael]

Hi, i finish to make the Table Export to JSON.

I use this code:

Public Sub ExportTableToJSON (Table As B4XTable) As List
    
    Dim data As List
    data.Initialize
    
    'Load B4XTable data
    Dim rs As ResultSet = Table.sql1.ExecQuery("SELECT * FROM data")
    
    Do While rs.NextRow
        Dim row As List
        row.Initialize
        Dim trow As Map
        trow.Initialize
        Dim c As B4XTableColumn
        For i = 0 To Table.Columns.Size - 1
            c = Table.Columns.Get(i)
            Select c.ColumnType
                Case Table.COLUMN_TYPE_DATE
                    trow.Put(c.Id,rs.GetLong(c.SQLID))
                Case Table.COLUMN_TYPE_NUMBERS
                    trow.Put(c.Id,rs.GetDouble(c.SQLID))
                Case Table.COLUMN_TYPE_TEXT
                    trow.Put(c.Id,rs.GetString(c.SQLID))
            End Select
        Next
        data.Add(trow)
    Loop
    
    rs.Close

    'Build the JSON Structure
    
    Dim jg As JSONGenerator
    jg.Initialize(CreateMap("Data": data))
    
    'Parse the JSON List   
    Dim JSON As JSONParser
    JSON.Initialize(jg.ToPrettyString(4))
    
    Dim Map1 As Map
    Map1.Initialize
    
    Map1 = JSON.NextObject
    
    'Return JSON Pretty String
    Dim nList As List
    nList.Initialize
    nList = Map1.Get("Data")

    Return nList

End Sub

For use it simple:

Dim JSONList as List = ExportTableToJSON(B4XTable1)

Its a simple code but powerful i use 1000 rows and it's really fast.
I hope help you.

Regard.