Oracle SQL JSON construct JSON Object


Your REST Client can consume JSON produced from a REST API. You can use Oracle SQL JSON functions to construct a JSON object and you can then use this to abstract out the source of the data. Your REST API client does not need to know that the data came from a set of tables. Here is an example of how to construct a JSON object.



WITH t AS (
    SELECT
                                    JSON_OBJECT ( 'measureDetails' VALUE (
                                        SELECT
                                            JSON_ARRAYAGG(JSON_OBJECT('measureName' VALUE b.measure_name, 'classifications' VALUE JSON_ARRAYAGG(JSON_OBJECT('classification'
                                            VALUE classification, 'value' VALUE b.value) RETURNING CLOB)) RETURNING CLOB)
                                        FROM
                                            all_analytic_view_base_meas    a,
                                            all_analytic_view_meas_class   b
                                        WHERE
                                            a.analytic_view_name = :analytic_view_name and
                                            a.owner = :owner and
                                            a.measure_name = b.measure_name
                                        GROUP BY
                                            b.measure_name
                                    ), 'hierDetails' VALUE (
                                        SELECT
                                            JSON_ARRAYAGG(JSON_OBJECT('hierName' VALUE a.hier_name, 'classifications' VALUE JSON_ARRAYAGG(JSON_OBJECT('classification'
                                            VALUE classification, 'value' VALUE b.value) RETURNING CLOB)) RETURNING CLOB)
                                        FROM
                                            all_analytic_view_hiers        a,
                                            all_analytic_view_hier_class   b
                                        WHERE
                                            a.dimension_alias = b.dimension_alias
                                            AND a.hier_alias = b.hier_alias
                                            and a.owner = :owner
                                            and a.analytic_view_name = :analytic_view_name
                                        GROUP BY
                                            a.hier_name
                                    ) ) avdetails
                                FROM
                                    dual
)
SELECT
    JSON_QUERY(avdetails, '$' RETURNING VARCHAR2(32000) PRETTY) pretty_avdetails
FROM
    t;


Here is the output

{
  "measureDetails" :
  [
    {
      "measureName" : "NUM_30_DAY_FILLS",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Number of 30 Day Fills"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Number of 30 Day Fills"
        }
      ]
    },
    {
      "measureName" : "NUM_30_DAY_FILLS_65",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Number of 30 Day Fills over 65"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Number of 30 Day Fills over 65"
        }
      ]
    },
    {
      "measureName" : "NUM_BENIFICIARIES",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Number of Beneficiaries"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Number of Beneficiaries"
        }
      ]
    },
    {
      "measureName" : "NUM_BENIFICIARIES_65",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Number of Beneficiaries over 65"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Number of Beneficiaries over 65"
        }
      ]
    },
    {
      "measureName" : "NUM_CLAIMS",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Number of Claims"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Number of Claims"
        }
      ]
    },
    {
      "measureName" : "NUM_CLAIMS_65",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Number of Claims over 65"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Number of Claims over 65"
        }
      ]
    },
    {
      "measureName" : "NUM_PRESCRIBERS",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Number of Prescribers"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Number of Prescribers"
        }
      ]
    },
    {
      "measureName" : "PAID_AMOUNT",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Paid Amount"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "$999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Paid Amount"
        }
      ]
    },
    {
      "measureName" : "PAID_AMOUNT_65",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Paid Amount over 65"
        },
        {
          "classification" : "FORMAT_STRING",
          "value" : "$999,999,999,999"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Paid Amount over 65"
        }
      ]
    }
  ],
  "hierDetails" :
  [
    {
      "hierName" : "MEDICARE_DRUG_HIER",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Drugs"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Drugs"
        }
      ]
    },
    {
      "hierName" : "MEDICARE_STATE_HIER",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "States"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "States"
        }
      ]
    },
    {
      "hierName" : "MEDICARE_YEAR_HIER",
      "classifications" :
      [
        {
          "classification" : "CAPTION",
          "value" : "Years"
        },
        {
          "classification" : "DESCRIPTION",
          "value" : "Years"
        }
      ]
    }
  ]
}

Comments

Post a Comment

Popular posts from this blog

Spring Framework

Spring is a J2EE framework and aims to simplify J2EE development Lightweight – A non invasive container that can assemble set of loosely coupled POJOs. This allows software components to be tested in isolation Abstract Transaction Manager – Allows for pluggable transaction managers, not tied to J2EE environments JDBC abstratcion layer – meaningfull exception hierarchy (no need to extract vendor codes from SQLException) simplified error handling. Integrartes with Toplink, Hibernate, JDO and iBATIS SQL maps AOP functionality – can AOP any object and add aspects such as declarative transaction management. Transaction management can be done without EJB or JTA A flexible MVC architecture – Can work with Struts, WebWork or Tapestry and accomodates multiple view techonlogies like JSP, Velocity, Tiles, iText and POI Spring uses a setter based dependancy injection pattern (Inversion of Control) See my Spring AOP Sample Book Reference: Pro Spring by Rob Harrop Jan Machacek
Read more

Never use a String

http://stephan.reposita.org/archives/2008/05/02/never-never-never-use-string-in-java-or-at-least-less-often/ An excellent post on why you should minimize using String, int and other Primitive Data Types.One advantage I personally see is in the Code Completion feature of Oracle JDeveloper where it will show me the data type for the methods but not the variable name. One should be careful of not overdoing this approach as it can make your code a lot more verbose.
Read more

JSON Web Token

JWT or JSON Web Tokens can be used for securely transferring data between two apps. You can use this to build a lightweight Single Signon functionality within your webapps. An Introduction to JSON Web Tokens in Plain English
Read more