oracle - PLSQL import JSON and export as a JSON file

Question

I am new to PL SQL. I have set up a virtual box with Database APP Development VM( I think it has everything I need inside, but not sure) Now I would like to load a json file as a table. (Then do somestuff) Then export it again

Here is the Json File:

    [
{
  "id" : 1,
  "created_at" : "Tue Oct 22 16:08:59 +0000 2013",
  "text" : "RT @Piratenpartei: Der @humanErr zur EU Datenschutzverordnung #EUDataP : “Starker EU-Datenschutz darf nicht scheitern” ",
  "lang" : "de",
  "place" : null,
  "retweet_count" : 10,
  "user" : {
    "created_at" : "Mon Jun 03 12:37:14 +0000 2013",
    "description" : "Der  Bezirksverband  Tübingen  im Landesverband Baden-Württemberg",
    "followers_count" : 122,
    "id" : 1479629599,
    "lang" : "de",
    "name" : "BzV Tübingen",
    "screen_name" : "BZV_Tuebingen",
    "url" : ""
  }
}, {
  "id" : 2,
  "created_at" : "Sat Oct 19 12:15:54 +0000 2013",
  "text" : "RT @PiratenBW: Unser nächster Landesparteitag findet am 15.02.-16.02 2014 in Heidelberg statt. /s3",
  "lang" : "de",
  "place" : null,
  "retweet_count" : 17,
  "user" : {
    "created_at" : "Mon Jun 03 12:37:14 +0000 2013",
    "description" : "Der  Bezirksverband  Tübingen  im Landesverband Baden-Württemberg",
    "followers_count" : 122,
    "id" : 1479629599,
    "lang" : "de",
    "name" : "BzV Tübingen",
    "screen_name" : "BZV_Tuebingen",
    "url" : ""
  }
}
]

By exporting i mean something like this: http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

Maybe some additional question. Is there something like automated import. If the file is larger, and it is not clear what is exactly inside or the documentation is poor.

Maybe I should added what databases software i have set up. I have got an account here; https://apex.oracle.com And I have an installation in Virtual Box of APP Development VM this is this one: https://www.oracle.com/downloads/developer-vm/community-downloads.html But maybe I need something else.

I am also looking into Postgre but this Question is regarding Oracle.

标签： oracleplsqloracle-sqldeveloperoracle12c

Answer 1

操作json的设置：

create table json_from_file (id number, data clob, constraint chk_data_is_json check (data is json));

insert into json_from_file (id, data)
  values ( 1, '{
  "id" : 1,
  "created_at" : "Tue Oct 22 16:08:59 +0000 2013",
  "text" : "RT @Piratenpartei: Der @humanErr zur EU Datenschutzverordnung #EUDataP : “Starker EU-Datenschutz darf nicht scheitern” ",
  "lang" : "de",
  "place" : null,
  "retweet_count" : 10,
  "user" : {
    "created_at" : "Mon Jun 03 12:37:14 +0000 2013",
    "description" : "Der  Bezirksverband  Tübingen  im Landesverband Baden-Württemberg",
    "followers_count" : 122,
    "id" : 1479629599,
    "lang" : "de",
    "name" : "BzV Tübingen",
    "screen_name" : "BZV_Tuebingen",
    "url" : ""
  }
}');
insert into json_from_file (id, data)
  values ( 2, '
{
  "id" : 2,
  "created_at" : "Sat Oct 19 12:15:54 +0000 2013",
  "text" : "RT @PiratenBW: Unser nächster Landesparteitag findet am 15.02.-16.02 2014 in Heidelberg statt. /s3",
  "lang" : "de",
  "place" : null,
  "retweet_count" : 17,
  "user" : {
    "created_at" : "Mon Jun 03 12:37:14 +0000 2013",
    "description" : "Der  Bezirksverband  Tübingen  im Landesverband Baden-Württemberg",
    "followers_count" : 122,
    "id" : 1479629599,
    "lang" : "de",
    "name" : "BzV Tübingen",
    "screen_name" : "BZV_Tuebingen",
    "url" : ""
  }
}');

更改 id 为 2 的 json：

DECLARE
  cl_data       CLOB;
  t_root_object JSON_OBJECT_T;
  t_user_object JSON_OBJECT_T;

  CURSOR cu_get_data is
  select data
    from json_from_file
   where id = 2;
BEGIN

   OPEN cu_get_data;
  FETCH cu_get_data INTO cl_data;
  CLOSE cu_get_data;

  t_root_object := JSON_OBJECT_T(cl_data);
  t_root_object.put('lang','ES'); -- changes "lang" in root object

  t_user_object := t_root_object.get_object('user');
  t_user_object.put('lang','ENG'); -- changes "lang" in root object.user

   cl_data := t_root_object.to_string;

   UPDATE json_from_file -- saving change in database
      SET data = cl_data
    WHERE id = 2;

   COMMIT;
end;

将所有部分重新组合在一起以便稍后导出：

create table exported_data(data clob);
/
declare

cursor cu_parts is
select data 
  from json_from_file;

whole_json clob;
begin

  whole_json := '['||chr(10);
  for json_part in cu_parts loop
    whole_json := whole_json||json_part.data;
  end loop;
  whole_json := chr(10)||']';

  insert into exported_data(data) values (whole_json);
  commit;
end;