Solved

Build JSON from table records

Posted on 2016-10-26
17
40 Views
Last Modified: 2016-10-27
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.
0
Comment
Question by:EddieShipman
  • 6
  • 5
  • 2
  • +3
17 Comments
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 41861241
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 41861244
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41861251
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41861261
Also, do you need strict typing, or can we represent the long numbers as character strings instead of very-large integers?
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 41861275
@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
 
LVL 26

Author Comment

by:EddieShipman
ID: 41861276
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41861286
OK, so it can be looked at as an array of arrays - titles on top and data rows below?
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 41861292
@Ray, this data will be queried from a table.
I guess you could say that.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 41861294
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
 
LVL 26

Author Comment

by:EddieShipman
ID: 41861314
You can make it work with an array because in C# it will be in a List object
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 83 total points
ID: 41861388
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
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 251 total points
ID: 41861453
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41861571
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
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 83 total points
ID: 41861655
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
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 83 total points
ID: 41862019
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
 
LVL 26

Author Closing Comment

by:EddieShipman
ID: 41862470
Thanks guys, Linq sure is cool...
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 41862473
Yep :)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now