0

Parse Dynamic Multiple Choice into JSON object

I have a DMC field that takes these values (dynamic value):

 

let all_entities := (select elements);
let my_event := this;
let my_elements := ((select elements where 'parent entry'.'testifies to events'.'testifies to event' like my_event) order by 'parent entry'.Id);
my_elements

 

 

The DMC shows (dynamic value name):

'has label'.label + ": " + text + " (entry: " + 'parent entry'.ID_entry + ")"

 

 

I'd want to have a JSON object that parses over all selected values

E.g.

[
    {
        "entry": ID_entry,
        "elements": [
            {
                "text": text,
                "label": label
            },
            {
                "text": text,
                "label": label
            }
        ]
    }
]

Mind that there can be multiple entries (ID_entry).

Currently, I only manage to parse this as a string and very awkwardly:

let myMi := numbers(Elements);
let myT := "";
let tmp_item := "";
for i in myMi do
    let tmp_item := (select elements where number(Id) = i);
    myT := myT + "," + """entry""" + ":" +
        tmp_item.'parent entry'.'testifies to events'.'testifies to event' +
        "," +
        """" +
        "elements" +
        """" +
        ": [{" +
        first("""text""" + ":" + """" + tmp_item.text + """, ""label""" + ":" + """" +
            tmp_item.'has label'.label +
            """") +
        "}]"
end;
myT := substr(myT, 1);
myT := "[{" + myT + "}]";
myT

A (externally formatted) example result is

[{
        "entry": 134,
        "elements": [{
                "text": "Per Obitum List",
                "label": "EVENT"
            }
        ],
        "entry": 134,
        "elements": [{
                "text": "Romae",
                "label": "LOCATION"
            }
        ],
        "entry": 134,
        "elements": [{
                "text": "Dominicus Marques",
                "label": "SUCCESSOR"
            }
        ]
    }
]

17 replies

null
    • Digital Humanities Researcher
    • Christoph.2
    • 3 mths ago
    • Reported - view

    I have tried, e.g., also

    let myMi := numbers(Elements);
let entries := [];
for i in myMi do
    let tmp_items := (select elements where number(Id) = i);
    if count(tmp_items) > 0 then
        let entryId := first(tmp_items).'parent entry'.'testifies to events'.'testifies to event';
        let elementsArray := [];
        for tmp_item in tmp_items do
            let element := {
                text: tmp_item.text,
                label: tmp_item.'has label'.label
            };
            elementsArray := elementsArray + element;
        end;
        let entry := {
            entry: entryId,
            elements: elementsArray
        };
        entries := entries + entry;
    end;
end;
formatJSON(entries)
    • Fred
    • 3 mths ago
    • Reported - view

    Thankfully Ninox has an easier way of creating JSONs. This method is very handy for dynamic printing and creating new DB within a your DB so you can create HTML tables.

    To start all you need is an array of nid or rid. So in your case you can start with:

    let myMi := for loop1 in numbers(Elements) do
     record(elements,loop1)
end;

    Then you can just to the following to create a json:

    myMi.{
     entry: ID_entry,
     2ndjsonname: fieldname2fromtable,
}

    You will see that Ninox will create a json for each record you select in your dMC.

    Now how do you add in a Many reference table?

    myMi.{
     entry: ID_entry,
     2ndjsonname: fieldname2fromtable,
     jsonName4referencetable: for loop2 in referencefieldname do
          {
              3rdjsonname: loop2.fieldname1,
              4thjsonname: loop2.fieldname3,
          }
     end
}

    So in the end you should see something like:

    let myMi := for loop1 in numbers(Elements) do
record(elements,loop1)
end;
myMi.{
entry: ID_entry,
2ndjsonname: fieldname2fromtable,
jsonName4referencetable: for loop2 in referencefieldname do
{
3rdjsonname: loop2.fieldname1,
4thjsonname: loop2.fieldname3,
}
end
}

    Here is an example from my DB:

    let getRecs := for loop1 in numbers('Pre-Production Runs') do
        record(Production,loop1)
    end;
getRecs.{
    recID: Id,
    Arts: Article;
    for loop2 in Article.'Article Line' do
        {
            artID: Article,
            matID: loop2.Materials
        }
    end
}

    And this is the results:

    [{"recID":"B19","Arts":[{"artID":1,"matID":2},{"artID":1,"matID":5}]},{"recID":"B20","Arts":[{"artID":1,"matID":2},{"artID":1,"matID":5}]}]

    • Digital Humanities Researcher
    • Christoph.2
    • 3 mths ago
    • Reported - view

    Thank you so much. This is certainly the way to go, yet, I don't manage to get what I need.

    I think it has to do with the DMC. It's on a table 'events' and lists 'elements' that are linked to 'entries' via 'parent entry'.

    so, the second loop in you example is relative to 'elements' but actually I would need to start from 'entries' so that the elements are grouped by entry.

    This code, e.g. does in a much better way something similar to my initial approach:

    let myMi := for loop1 in numbers(Elements) do
        record(elements,loop1)
    end;
myMi.{
    entry: 'parent entry',
    elements: {
        text: text,
        label: 'has label'.label
    }
}

    Result:

    [{"entry":1212,"elements":{"text":"Sancti Stephani de oculo","label":"PLACE"}},{"entry":1212,"elements":{"text":"24 cum incertis 40 D","label":"FINANCIAL"}},{"entry":1213,"elements":{"text":"Romae","label":"LOCATION"}},{"entry":1213,"elements":{"text":"Oxonen","label":"DIOCESE"}},{"entry":1213,"elements":{"text":"Joannes albarez","label":"SUCCESSOR"}}]

    Any idea how I can put this upside down?

    entry, elements: [{label, text},{label, text}],...

    • Digital Humanities Researcher
    • Christoph.2
    • 3 mths ago
    • Reported - view
    let myMi := for loop1 in numbers(Elements) do
        record(entries,loop1)
    end;
myMi.{
    entry: trancription
}

    No code error but [] result.

      • Fred
      • 3 mths ago
      • Reported - view

      When I don't have any choices selected in the dMC I get:

      []

      When I have selections in the dMC but the field is empty I see:

      [{},{}]

    • Fred
    • 3 mths ago
    • Reported - view
     said:
    Any idea how I can put this upside down?
    entry, elements: [{label, text},{label, text}],...

     you just swap label and text.

    myMi.{
    entry: 'parent entry',
    elements: {
        label: 'has label'.label,
        text: text,
    }
}
      • Digital Humanities Researcher
      • Christoph.2
      • 3 mths ago
      • Reported - view

      Sorry for the confusion.

      I have choices selected in the dMC and yet get [].

      The swap of text and label was not the issue but the elements get grouped by entry instead of the other way around (as currently).

      An example to maybe better understand my data structure:

      [{"entries":[1212,1213]},{"entries":[1212,1213]},{"entries":[1212,1213]},{"entries":[1212,1213]},{"entries":[1212,1213]}]

      is produced by
       

      let myMi := for loop1 in numbers(Elements) do
        record(elements,loop1)
    end;
myMi.{
    entries: unique('parent entry'.'testifies to'.event.'is testified by'.entry)
}

      I have five dMC options checked (out of 20)

      • Rafael Sanchis
      • Rafael_Sanchis
      • 3 mths ago
      • Reported - view

       Hi Fred

      There are some Database example to understand better this.

    • Fred
    • 3 mths ago
    • Reported - view
     said:
    The swap of text and label was not the issue but the elements get grouped by entry instead of the other way around (as currently).

     Everything is grouped by record in elements, as that is what myMi is based on.

    You might need create another variable before you start building JSON.

    Looks like you want create a JSON based on each instance of entry from a selection that is based on records in elements.

    'parent entry'.'testifies to'.event.'is testified by'.entry

    So you could try:

    let myMi := for loop1 in numbers(Elements) do
        record(elements,loop1)
    end;
let myMientries := unique(myMi.'parent entry'.'testifies to'.event.'is testified by'.entry);
myMientries.{
    entry: Id,
    }

    It looks like entry is a reference field as well so you are now starting from that table and all references to fields start from there.

    If I'm way off, maybe you can post a sample (no personal data) DB and I can get a better understanding of what is going on.

    • Digital Humanities Researcher
    • Christoph.2
    • 3 mths ago
    • Reported - view

    Yes, this did the trick! Wonderful.

    let myMi := for loop1 in numbers(Elements) do
        record(elements,loop1)
    end;
let myMientries := unique(myMi.'parent entry');
myMientries.{
    entry: number(ID),
    elements: for loop2 in myMi do
        {
            Id: loop2.number(Id),
            text: loop2.text,
            label: loop2.'has label'.label
        }
    end
}

    will lead to

    [{
        "entry": 1212,
        "elements": [{
                "Id": 15620,
                "text": "Sancti Stephani de oculo",
                "label": "PLACE"
            }, {
                "Id": 15624,
                "text": "24 cum incertis 40 D",
                "label": "FINANCIAL"
            }, {
                "Id": 15626,
                "text": "Romae",
                "label": "LOCATION"
            }, {
                "Id": 15627,
                "text": "Oxonen",
                "label": "DIOCESE"
            }, {
                "Id": 15628,
                "text": "Joannes albarez",
                "label": "SUCCESSOR"
            }
        ]
    }, {
        "entry": 1213,
        "elements": [{
                "Id": 15620,
                "text": "Sancti Stephani de oculo",
                "label": "PLACE"
            }, {
                "Id": 15624,
                "text": "24 cum incertis 40 D",
                "label": "FINANCIAL"
            }, {
                "Id": 15626,
                "text": "Romae",
                "label": "LOCATION"
            }, {
                "Id": 15627,
                "text": "Oxonen",
                "label": "DIOCESE"
            }, {
                "Id": 15628,
                "text": "Joannes albarez",
                "label": "SUCCESSOR"
            }
        ]
    }
]
    • Digital Humanities Researcher
    • Christoph.2
    • 3 mths ago
    • Reported - view

    Ah, not quite!

    The issue is that entry 1212 has two elements and 1213 three. So, these are not assigned based on the 'parent entry' but are all repeated for both entries.

    • Digital Humanities Researcher
    • Christoph.2
    • 3 mths ago
    • Reported - view

    Maybe that helps, take a look at the dMC directly that digest the relevant information

    • Digital Humanities Researcher
    • Christoph.2
    • 3 mths ago
    • Reported - view

    I guess this is it:
     

    let myMi := for loop1 in numbers(Elements) do
        record(elements,loop1)
    end;
let myMientries := unique(myMi.'parent entry');
myMientries.{
    entry: number(ID),
    elements: for loop2 in myMi do
        if loop2.'parent entry' = ID then
            {
                Id: loop2.number(Id),
                text: loop2.text,
                label: loop2.'has label'.label
            }
        end
    end
}
      • Fred
      • 3 mths ago
      • Reported - view

      glad it all worked out.

      Your code shows how you can build a quick db from data from other tables and relationships.

      • Fred
      • 3 mths ago
      • Reported - view

      When you get a chance please mark this post answered. :)

      • Digital Humanities Researcher
      • Christoph.2
      • 3 mths ago
      • Reported - view

      Sorry for the late follow-up (and it's not worth another topic): how can I set up a dynamic dictionary, like choosing a field name from the database and not just the value, e.g.

      {mytable.text: loop2.text}
      • Digital Humanities Researcher
      • Christoph.2
      • 3 mths ago
      • Reported - view

       

      var my_colors := {};
for colors in select labels do
    let fieldName := colors.label;
    let my_color := colors.'label category'.Color;
    setItem(my_colors, fieldName, my_color)
end;
my_colors

      Got it!

Login to reply

Content aside

  • Status Answered
  • 3 mths agoLast active
  • 17Replies
  • 113Views
  • 4 Following