Build JSON from table records

I get this data (csv formatted here) from a query.
"Webinarkey",        "RegistrantKey",     "PID",  "FullName"
4605887570527930114, 5668181830364643852, 909428, "Taylor Swifth",
5551459873814116097, 6629935645682877452, 909428, "Taylor Swifth",
7184734918871838978, 7781215580043136268, 909428, "Taylor Swifth",
3211296708669449729, 2705374026203470860, 909428, "Taylor Swifth",
1711808039457813249, 4033024420806207757, 909428, "Taylor Swifth",
1239773404021476866, 4653789994609595403, 909428, "Taylor Swifth",
4300862154261746178, 8978590339606068235, 909428, "Taylor Swifth",
8227760236281352705, 1170319454386360844, 909428, "Taylor Swifth",
1217612747145926657, 2082471002006026253, 909428, "Taylor Swifth",
9113395961717620484, 612206252367243021,  909428, "Taylor Swifth"
1217612747145926657, 7967558611165451531, 914251, "Katie Perry",
1711808039457813249, 1787578684817884171, 914251, "Katie Perry",
1239773404021476866, 3566719340901889036, 914251, "Katie Perry",
4300862154261746178, 6675608259647855116, 914251, "Katie Perry",
7184734918871838978, 3466720957533842189, 914251, "Katie Perry",
3211296708669449729, 6923047853794082829, 914251, "Katie Perry",
9113395961717620484, 640247097410423309,  914251, "Katie Perry",
5551459873814116097, 202236849192755211,  914251, "Katie Perry",
8227760236281352705, 623542217498013964,  914251, "Katie Perry",
4605887570527930114, 7202114900321460491, 914251, "Katie Perry"

Open in new window

I need to take the above data and loop through to create this JSON:
{
   "Webinars":[
      {
         "PID":909428,
         "FullName":"Taylor Swifth",
         "Attendances":[
            {
               "WebinarKey":4605887570527930114,
               "RegistrantKey":5668181830364643852
            },
            {
               "WebinarKey":5551459873814116097,
               "RegistrantKey":6629935645682877452
            },
            {
               "WebinarKey":7184734918871838978,
               "RegistrantKey":7781215580043136268
            },
            {
               "WebinarKey":3211296708669449729,
               "RegistrantKey":2705374026203470860
            },
            {
               "WebinarKey":1711808039457813249,
               "RegistrantKey":4033024420806207757
            },
            {
               "WebinarKey":1239773404021476866,
               "RegistrantKey":4653789994609595403
            },
            {
               "WebinarKey":4300862154261746178,
               "RegistrantKey":8978590339606068235
            },
            {
               "WebinarKey":8227760236281352705,
               "RegistrantKey":1170319454386360844
            },
            {
               "WebinarKey":1217612747145926657,
               "RegistrantKey":2082471002006026253
            },
            {
               "WebinarKey":9113395961717620484,
               "RegistrantKey":612206252367243021
            }
         ]
      },
      {
         "PID":914251,
         "FullName":"Katie Perry",
         "Attendances":[
            {
               "WebinarKey":1217612747145926657,
               "RegistrantKey":7967558611165451531
            },
            {
               "WebinarKey":1711808039457813249,
               "RegistrantKey":1787578684817884171
            },
            {
               "WebinarKey":1239773404021476866,
               "RegistrantKey":3566719340901889036
            },
            {
               "WebinarKey":4300862154261746178,
               "RegistrantKey":6675608259647855116
            },
            {
               "WebinarKey":7184734918871838978,
               "RegistrantKey":3466720957533842189
            },
            {
               "WebinarKey":3211296708669449729,
               "RegistrantKey":6923047853794082829
            },
            {
               "WebinarKey":9113395961717620484,
               "RegistrantKey":640247097410423309
            },
            {
               "WebinarKey":5551459873814116097,
               "RegistrantKey":202236849192755211
            },
            {
               "WebinarKey":8227760236281352705,
               "RegistrantKey":623542217498013964
            },
            {
               "WebinarKey":4605887570527930114,
               "RegistrantKey":7202114900321460491
            }
         ]
      }
   ]
}

Open in new window

I can handle C# or PHP code.
LVL 26
Eddie ShipmanAll-around developerAsked:
Who is Participating?
 
Chris StanyonConnect With a Mentor Commented:
You say that in C# you're going to have a List. If you set up your list correctly, then you can use LINQ to group your data, and then pass that to the Json.Net library to convert your list.

You would need to create a Webinar class to hold each line of data.

public class Webinar
{
    public string WebinarKey { get; set; }
    public string RegistrantKey { get; set; }
    public string PID { get; set; }
    public string FullName { get; set; }
}

Open in new window

Create a List<Webinar> and populate it when you read in your data. You wil then have a strongly typed List of Webinar objects.

List<Webinar> webinarList = new List<Webinar>();
webinarList.Add( new Webinar() { ... } );
...

Open in new window

Once you've got that then the LINQ and JSON would look something like this:

var webinars = new {
    Webinars = webinarList.GroupBy(x => new { x.PID, x.FullName }).
    Select(y => new
    {
        PID = y.Key.PID,
        FullName = y.Key.FullName,
        Attendances = y.Select(z => new
        {   
            WebinarKey = z.WebinarKey,
            RegistrantKey = z.RegistrantKey
        })
    })
};

string json = JsonConvert.SerializeObject(webinars);

Open in new window

0
 
Tapan PattanaikSenior EngineerCommented:
Hi EddieShipman,

You can handle in C#. For that you have to use Json.NET

You can download the .dll from the below URL

Example :

Product product = new Product();
product.Name = "Apple";
product.Expiry = new DateTime(2008, 12, 28);
product.Sizes = new string[] { "Small" };

string json = JsonConvert.SerializeObject(product);

// {
//   "Name": "Apple",
//   "Expiry": "2008-12-28T00:00:00",
//   "Sizes": [
//     "Small"
//   ]
// }

http://www.newtonsoft.com/json
0
 
Eddie ShipmanAll-around developerAuthor Commented:
Sorry, Tapan, that example is too simple for what I need, please examine the data in the post. I know how to use Json.Net.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Ray PaseurCommented:
Eddie: Is this in a file somewhere?  Or can you tell us how the data sets were acquired?  The commas seem to indicate that there are three separate collections.
0
 
Ray PaseurCommented:
Also, do you need strict typing, or can we represent the long numbers as character strings instead of very-large integers?
0
 
Eddie ShipmanAll-around developerAuthor Commented:
@Ray,
The data has commas at the end of the line when it shouldn't. It should be straight CSV showing, I will modify. It doesn't matter as I will most likely be converting to C#.
0
 
Eddie ShipmanAll-around developerAuthor Commented:
I hate that they won't let you edit your own question...
"Webinarkey",        "RegistrantKey",     "PID",  "FullName"
4605887570527930114, 5668181830364643852, 909428, "Taylor Swift"
5551459873814116097, 6629935645682877452, 909428, "Taylor Swift"
7184734918871838978, 7781215580043136268, 909428, "Taylor Swift"
3211296708669449729, 2705374026203470860, 909428, "Taylor Swift"
1711808039457813249, 4033024420806207757, 909428, "Taylor Swift"
1239773404021476866, 4653789994609595403, 909428, "Taylor Swift"
4300862154261746178, 8978590339606068235, 909428, "Taylor Swift"
8227760236281352705, 1170319454386360844, 909428, "Taylor Swift"
1217612747145926657, 2082471002006026253, 909428, "Taylor Swift"
9113395961717620484, 612206252367243021,  909428, "Taylor Swift"
1217612747145926657, 7967558611165451531, 914251, "Katie Perry"
1711808039457813249, 1787578684817884171, 914251, "Katie Perry"
1239773404021476866, 3566719340901889036, 914251, "Katie Perry"
4300862154261746178, 6675608259647855116, 914251, "Katie Perry"
7184734918871838978, 3466720957533842189, 914251, "Katie Perry"
3211296708669449729, 6923047853794082829, 914251, "Katie Perry"
9113395961717620484, 640247097410423309,  914251, "Katie Perry"
5551459873814116097, 202236849192755211,  914251, "Katie Perry"
8227760236281352705, 623542217498013964,  914251, "Katie Perry"
4605887570527930114, 7202114900321460491, 914251, "Katie Perry"

Open in new window

0
 
Ray PaseurCommented:
OK, so it can be looked at as an array of arrays - titles on top and data rows below?
0
 
Eddie ShipmanAll-around developerAuthor Commented:
@Ray, this data will be queried from a table.
I guess you could say that.
0
 
Ray PaseurCommented:
OK, then it's a results set  that would be accessed with some kind of iterator, retrieving each row one at a time.  I think I can work with that.  Back in a few moments...
0
 
Eddie ShipmanAll-around developerAuthor Commented:
You can make it work with an array because in C# it will be in a List object
0
 
Ray PaseurConnect With a Mentor Commented:
This looks sensible to me.  Some of the data transformation may not be needed, depending on how the data actually makes its way into the program.  If this were a MySQL results set, it would look like the data that is in $rows after line 47.  The $rows need to be ordered by FullName.
https://iconoun.com/demo/temp_eddieshipman.php
<?php // demo/temp_eddieshipman.php
/**
 * https://www.experts-exchange.com/questions/28979153/Build-JSON-from-table-records.html
 *
 * Assumes that the rows are ordered by "FullName"
 */
error_reporting(E_ALL);
echo '<pre>';


// SIMULATED QUERY RESULTS SET
$sets =
[
[ "Webinarkey",        "RegistrantKey",     "PID",  "FullName" ],
[ 4605887570527930114, 5668181830364643852, 909428, "Taylor Swift" ],
[ 5551459873814116097, 6629935645682877452, 909428, "Taylor Swift" ],
[ 7184734918871838978, 7781215580043136268, 909428, "Taylor Swift" ],
[ 3211296708669449729, 2705374026203470860, 909428, "Taylor Swift" ],
[ 1711808039457813249, 4033024420806207757, 909428, "Taylor Swift" ],
[ 1239773404021476866, 4653789994609595403, 909428, "Taylor Swift" ],
[ 4300862154261746178, 8978590339606068235, 909428, "Taylor Swift" ],
[ 8227760236281352705, 1170319454386360844, 909428, "Taylor Swift" ],
[ 1217612747145926657, 2082471002006026253, 909428, "Taylor Swift" ],
[ 9113395961717620484, 612206252367243021,  909428, "Taylor Swift" ],
[ 1217612747145926657, 7967558611165451531, 914251, "Katie Perry" ],
[ 1711808039457813249, 1787578684817884171, 914251, "Katie Perry" ],
[ 1239773404021476866, 3566719340901889036, 914251, "Katie Perry" ],
[ 4300862154261746178, 6675608259647855116, 914251, "Katie Perry" ],
[ 7184734918871838978, 3466720957533842189, 914251, "Katie Perry" ],
[ 3211296708669449729, 6923047853794082829, 914251, "Katie Perry" ],
[ 9113395961717620484, 640247097410423309,  914251, "Katie Perry" ],
[ 5551459873814116097, 202236849192755211,  914251, "Katie Perry" ],
[ 8227760236281352705, 623542217498013964,  914251, "Katie Perry" ],
[ 4605887570527930114, 7202114900321460491, 914251, "Katie Perry" ],
]
;

// TURN THE ROWS INTO AN ARRAY OF OBJECTS
$keys = $sets[0];
unset($sets[0]);

$rows = [];
foreach ($sets as $data)
{
    $new = (object)array_combine($keys, $data);
    $rows[] = $new;
}


// COLLECT A LIST OF ARTISTS
$artists = [];
foreach ($rows as $row)
{
    $artists[$row->FullName] = $row->FullName;
}


// COLLECTION HERE
$webinars = [];

// COLLECT EACH ARTIST INDIVIDUALLY
foreach ($artists as $artist)
{
    $attendances = [];
    foreach ($rows as $row)
    {
        // DO ONE ARTIST AT A TIME
        if ($artist != $row->FullName) continue;

        // KEEP ONLY PID, FullName
        $webinar = clone $row;
        unset($webinar->$keys[0]);
        unset($webinar->$keys[1]);

        // KEEP ONLY Webinarkey, RegistrantKey
        $atts = clone $row;
        unset($atts->$keys[2]);
        unset($atts->$keys[3]);

        // APPEND TO Attendances ARRAY
        $attendances[] = $atts;
        $webinar->Attendances = $attendances;
    }

    // SAVE THIS ARTIST WEBINAR COLLECTION
    $webinars[] = $webinar;
}

// WRAP UP AN OBJECT TO MATCH THE POST AT E-E
$obj = new StdClass;
$obj->Webinars = $webinars;

// MAKE JSON AND SHOW THE WORK PRODUCT
$out = json_encode($obj, JSON_PRETTY_PRINT);
echo $out;

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi Eddie;

The following code snippet using Linq will read a csv file, strip the header, split each line, group the data and create the classes that Newtonsoft will use to create the Json object.
char[] charsToTrim =  {' ', '\"'};

var res = (from string line in File.ReadAllLines("Data.csv")
           where !line.StartsWith("\"Webinarkey")
           let fields = line.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList()
           let groupFields = new { Webinarkey = fields[0], RegistrantKey = fields[1], PID = fields[2], FullName = fields[3] }
           group groupFields by groupFields.PID.Trim() into grouping
           select new Rootobject
           {
               Webinars = new Webinar[] {
                   new Webinar { PID = int.Parse(grouping.Key),
                                 FullName = grouping.Select(g => g.FullName ).First().Trim(charsToTrim),
                                 Attendances = (from att in grouping
                                                select new Attendance {
                                                    WebinarKey = long.Parse(att.Webinarkey),
                                                    RegistrantKey = long.Parse(att.RegistrantKey)
                                                }).ToArray()
                               }
               }
           }).ToList();

string theJasonObject = JsonConvert.SerializeObject(res);

Open in new window

0
 
Julian HansenConnect With a Mentor Commented:
This should give you the expected results.
Example assumes data is in a file attendance_data.csv
<?php
$filename = 'attendance_data.csv';
$file = fopen($filename, 'r');
// IGNORE FIRST LINE
fgetcsv($file, 10000);

$results = array();
while($row = fgetcsv($file, 1000)) {
  // USE THE PID AS A KEY FOR NOW
  // CREATE A NEW ELEMENT IF PID NOT 
  // ALREADY IN RESULT SET
  $pid = trim($row[2]);
  if (!isset($results[$pid])) {
    $newItem = new stdClass;
    $newItem->PID = $pid;
    $newItem->FullName = trim($row[3]);
    $newItem->Attendances = array();
    $results[$pid] = $newItem;
  }
  // PID EXISTS SO ADD THE ATTENDANCE
  $newAttendance = new stdClass;
  $newAttendance->WebinarKey = trim($row[0]);
  $newAttendance->RegistrantKey = trim($row[1]);
  $results[$pid]->Attendances[] = $newAttendance;
}
// STRIP OUT THE PID KEYS
$results = array_values($results);

// ADD THE PARENT PROPERTY
$output = new stdClass;
$output->Webinars = $results;
echo json_encode($output);

Open in new window

Working sample here
Edit Minor bug fixed in php code
0
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi Eddie;

I re-posted my solution because I forgot to show the three classes needed with Newtonsoft API.
using Newtonsoft.Json;


char[] charsToTrim =  {' ', '\"'};

var res = (from string line in File.ReadAllLines("Data.csv")
            where !line.StartsWith("\"Webinarkey")
            let fields = line.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList()
            let groupFields = new { Webinarkey = fields[0], RegistrantKey = fields[1], PID = fields[2], FullName = fields[3] }
            group groupFields by groupFields.PID.Trim() into grouping
            select new Rootobject
            {
                Webinars = new Webinar[] {
                    new Webinar { PID = int.Parse(grouping.Key),
                                    FullName = grouping.Select(g => g.FullName ).First().Trim(charsToTrim),
                                    Attendances = (from att in grouping
                                                select new Attendance {
                                                    WebinarKey = long.Parse(att.Webinarkey),
                                                    RegistrantKey = long.Parse(att.RegistrantKey)
                                                }).ToArray()
                                }
                }
            }).ToList();

string json = JsonConvert.SerializeObject(res);   
   
   
public class Rootobject {
    public Webinar[] Webinars { get; set; }
}

public class Webinar {
    public int PID { get; set; }
    public string FullName { get; set; }
    public Attendance[] Attendances { get; set; }
}

public class Attendance {
    public long WebinarKey { get; set; }
    public long RegistrantKey { get; set; }
}

Open in new window

0
 
Eddie ShipmanAll-around developerAuthor Commented:
Thanks guys, Linq sure is cool...
0
 
Chris StanyonCommented:
Yep :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.