Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extract field values from JSON string using PL/SQL

LauryFeb 19 2018 — edited Feb 22 2018

Hi,

I am trying to extract the fields and the values of these fields from a JSON string with a PL/SQL piece of code:

set serveroutput on

declare

    l_json_text varchar2(32767);

    l_count     pls_integer;

    l_members   wwv_flow_t_varchar2;

    l_paths     apex_t_varchar2;

    l_exists    boolean;

begin

    ---

    l_json_text := '{

        "items":[

            {"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1983-01-11T23:00:00Z","sal":1100,"comm":null,"deptno":20},

            {"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-08T22:00:00Z","sal":2450,"comm":null,"deptno":10},

            {"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-22T23:00:00Z","sal":1300,"comm":null,"deptno":10}

        ]}';

    ---

    apex_json.parse(l_json_text);

    ---

    l_count := APEX_JSON.get_count(p_path => 'items');

    dbms_output.put_line('Members count: ' || l_count);

    ---

    for i in 1 .. l_count

    loop

        dbms_output.put_line('Employee Number: ' ||

        -- apex_json.get_number(p_path => 'items.empno[%d].empno', p0 => i));

        -- apex_json.get_number(p_path => 'items.empno[%d]', p0 => i));

        apex_json.get_number(p_path => 'items.empno.empno[%d]', p0 => i));

    end loop;

end;

/

I can get the number of elements of the type "empno", but I am unable to get the values for "empno", "ename",...

Does someone know how to extract these values?

Kind Regards

Comments

bouye-JavaNet
Answer

It's not that hard to do it:

public class Main extends Application {

    @Override

    public void start(final Stage primaryStage) {

        // Creates items for menu button.

        final ToggleGroup toogleGroup = new ToggleGroup();

        final MenuItem[] menus = IntStream.range(0, 10)

                .mapToObj(index -> {

                    final RadioMenuItem menuItem = new RadioMenuItem();

                    menuItem.setText(String.format("Radio #%d", index + 1));

                    menuItem.setToggleGroup(toogleGroup);

                    menuItem.setOnAction(event -> System.out.printf("Action -> %s%n", menuItem.getText()));

                    return menuItem;

                })

                .toArray(MenuItem[]::new);

        toogleGroup.getToggles().get(0).setSelected(true);

        // Creates menu button.

        final SplitMenuButton splitMenuButton = new SplitMenuButton();

        // Forward action to selected item when button is clicked.

        // Works ok but logs warnings when the selected toggle is briefly null when the selection changes.

//        splitMenuButton.onActionProperty().bind(Bindings.select(toogleGroup.selectedToggleProperty(), "onAction"));

        // Works ok without warnings.

        splitMenuButton.setOnAction(event -> {

            Optional.ofNullable((RadioMenuItem) toogleGroup.getSelectedToggle())

                    .ifPresent(menuItem -> {

                        Optional.ofNullable(menuItem.getOnAction())

                                .ifPresent(eventHandler -> eventHandler.handle(event));

                    });

        });

        // Bind item's text to button.

        splitMenuButton.textProperty().bind(new StringBinding() {

            {

                bind(toogleGroup.selectedToggleProperty());

            }

            @Override

            public void dispose() {

                unbind(toogleGroup.selectedToggleProperty());

                super.dispose();

            }

            @Override

            protected String computeValue() {

                final RadioMenuItem menuItem = (RadioMenuItem) toogleGroup.getSelectedToggle();

                String result = (menuItem == null) ? null : menuItem.getText();

                return result;

            }

        });

        // Bind item's graphic to button.

        splitMenuButton.graphicProperty().bind(new ObjectBinding<Node>() {

            {

                bind(toogleGroup.selectedToggleProperty());

            }

            @Override

            public void dispose() {

                unbind(toogleGroup.selectedToggleProperty());

                super.dispose();

            }

            @Override

            protected Node computeValue() {

                final RadioMenuItem menuItem = (RadioMenuItem) toogleGroup.getSelectedToggle();

                final Node result = null;

                // Find a way to duplicate graphic here.

                // final Node result = (menuItem == null) ? null : duplicateGraphic(menuItem.getGraphic());

                return result;

            }

        });

        splitMenuButton.getItems().setAll(menus);

        final StackPane root = new StackPane();

        root.getChildren().add(splitMenuButton);

        final Scene scene = new Scene(root, 300, 250);

        primaryStage.setTitle("Test");

        primaryStage.setScene(scene);

        primaryStage.show();

    }

    public static void main(final String[] args) {

        launch(args);

    }

}

Marked as Answer by Muzib · Sep 27 2020
Muzib

Wow! works fine. I appreciate it. Thanks a lot.

@

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 20 2018
Added on Feb 19 2018
5 comments
3,838 views