none
How to create table for Complex JSON RRS feed

  • Question

  • Dear All.


    i have data as below :-how can we create table with columns :-

    Identifier and active that contains all info as below :-

    {
        "identifier": [{
            "use": "usual",
            "type": {
                "coding": [{
                    "code": "MR"
                }]
            },
            "value": "12345",
            "period": {
                "start": "2001-05-06"
            },
            "assigner": {
                "display": "Acme Healthcare"
            }
        }],
        "active": true
    }

    Thanks

    HadoopHelp

    Friday, January 24, 2020 2:00 PM

All replies

  • Hi HadoopHelp

    This document will be a great start to get more information regarding what you are trying to achieve. 

    https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

    PLease do revert with your questions. 

    Regards
    Navtej S

    Friday, January 24, 2020 9:44 PM
    Moderator
  • Hi HadoopHelp

    Please let us know if you need any further assistance regarding this.

    Thanks
    Navtej S

    Monday, January 27, 2020 3:07 PM
    Moderator
  • Hi Thanks.

    but still issue is same.

    there is no any use case related with my below query.

    please help.i need table schema for below data:-

    {
      "resourceType" : "Patient",
      // from Resource: id, meta, implicitRules, and language
      // from DomainResource: text, contained, extension, and modifierExtension
      "identifier" : [{ Identifier }], // An identifier for this patient
      "active" : <boolean>, // Whether this patient's record is in active use
      "name" : [{ HumanName }], // A name associated with the patient
      "telecom" : [{ ContactPoint }], // A contact detail for the individual
      "gender" : "<code>", // male | female | other | unknown
      "birthDate" : "<date>", // The date of birth for the individual
      // deceased[x]: Indicates if the individual is deceased or not. One of these 2:
      "deceasedBoolean" : <boolean>,
      "deceasedDateTime" : "<dateTime>",
      "address" : [{ Address }], // An address for the individual
      "maritalStatus" : { CodeableConcept }, // Marital (civil) status of a patient
      // multipleBirth[x]: Whether patient is part of a multiple birth. One of these 2:
      "multipleBirthBoolean" : <boolean>,
      "multipleBirthInteger" : <integer>,
      "photo" : [{ Attachment }], // Image of the patient
      "contact" : [{ // A contact party (e.g. guardian, partner, friend) for the patient
        "relationship" : [{ CodeableConcept }], // The kind of relationship
        "name" : { HumanName }, // A name associated with the contact person
        "telecom" : [{ ContactPoint }], // A contact detail for the person
        "address" : { Address }, // Address for the contact person
        "gender" : "<code>", // male | female | other | unknown
        "organization" : { Reference(Organization) }, // C? Organization that is associated with the contact
        "period" : { Period } // The period during which this contact person or organization is valid to be contacted relating to this patient
      }],
      "communication" : [{ // A language which may be used to communicate with the patient about his or her health
        "language" : { CodeableConcept }, // R!  The language which can be used to communicate with the patient about his or her health
        "preferred" : <boolean> // Language preference indicator
      }],
      "generalPractitioner" : [{ Reference(Organization|Practitioner|
       PractitionerRole) }], // Patient's nominated primary care provider
      "managingOrganization" : { Reference(Organization) }, // Organization that is the custodian of the patient record
      "link" : [{ // Link to another patient resource that concerns the same actual person
        "other" : { Reference(Patient|RelatedPerson) }, // R!  The other patient or related person resource that the link refers to
        "type" : "<code>" // R!  replaced-by | replaces | refer | seealso
      }]
    }

    Thanks

    HadoopHelp

    Thursday, January 30, 2020 3:21 PM
  • I hope this example helps you a little bit:

    In the following example, the query uses both relational and JSON data (stored in a column named jsonCol) from a table:

    SQL<button aria-label="Copy code" class="action is-relative" data-bi-name="copy" style="box-sizing:inherit;font-family:inherit;font-size:12.8px;margin:0px;cursor:pointer;padding:2px 10px;border-left-width:1px;display:flex;-webkit-box-align:center;align-items:center;" type="button">Copy
    </button>
     0
      AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
      AND Status = 'Active'
    ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode')
    " style="box-sizing:inherit;font-family:SFMono-Regular, Consolas, 'Liberation Mono', Menlo, Courier, monospace;font-size:1em;direction:ltr;;border:0px;padding:0px;display:block;line-height:19px;">SELECT Name, Surname,
      JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
      JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' '
      + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
      JSON_QUERY(jsonCol, '$.info.skills') AS Skills
    FROM People
    WHERE ISJSON(jsonCol) > 0
      AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
      AND Status = 'Active'
    ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode')
    

    Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON functions use JavaScript-like syntax for referencing values inside JSON text.

    Thursday, January 30, 2020 3:27 PM
  • Thanks Ford.

    but i want to create table schema(table_fhir) for that data. but data is available in different tables column.

    above all green value is available in some tables column.

     "identifier" : [{ Identifier }], // An identifier for this patient
      "active" : <boolean>, // Whether this patient's record is in active use
      "name" : [{ HumanName }], // A name associated with the patient
      "telecom" : [{ ContactPoint }], // A contact detail for the individual

    above identifier and active name etc... will become new column with new table_fhir and data type will change as you can see there.

    and with

    "identifier" : [{ Identifier }] //list may contains multiple column from diff table columns etc..

    Note: we want to create a table that satisfied the above data structure.

    Thanks

    HadoopHelp

    Friday, January 31, 2020 7:49 AM
  • Hi 

    Please convey from where this data is being sourced. Are you trying to extract this from source system and what tools you are using to do so.

    This will help us in understanding your situation better so that we can help you.

    Thanks
    Navtej S

    Monday, February 3, 2020 10:22 PM
    Moderator
  • Hi Navtej.

    thanks for reply.

    leave all previous discussion.

    below is the require data schema.

     "name": [ //need/require to create table schema a "name"column that contains all below fields
        {
          "use": "official",   //"use"is column in table1
          "family": "Chalmers",//"family"is column in table2
          "given": [         //"given"is column in table3
            "Peter",
            "James"
          ]
        }]

    We are taking that data from different tables and trying create tables schema that contains all data.

    Thanks

    HadoopHelp

    Wednesday, February 5, 2020 9:20 AM
  • Hi 

    We did try to connect with Product developers for this issue. Again your question is unclear to them as well.

    To them it seems you want to go from a JSON document to create table definition. They conveyed you could use multiple OPENJSON queries to extract a relational rowset out of the JSON document and persist into a table. They also said, your final example of json document says which element belongs to which table & so on but unclear how that determination was made. To them it seems that this is not a SQL problem at all.

    So you should provide sample data, DDL & expected output like that to work on it further. Otherwise the requirements are unclear to resolve this issue.

    Thanks
    Navtej S

    Wednesday, February 19, 2020 1:26 PM
    Moderator