sql query oracle to fit a tree structure format

Question

I have created these sample tables to create a json tree structure so that i can use jqtree to create a tree layout.

I want my json to be in the format

[
  {"id":1, "parentid": 0, "name": "Carnivores"},
  {"id":2, "parentid": 0, "name": "Herbivores"},
  {"id":3, "parentid": 1, "name": "Dogs"},
  {"id":4, "parentid": 3, "name": "Labradors"},  
  {"id":5, "parentid": 3, "name": "Pugs"},
  {"id":6, "parentid": 3, "name": "Terriers"}

]

The tables are as follows.

| catg_id       |   catg_name       | 
| —————-        |————————-          |
| 1             |   Carnivores      |
| 2             |   Herbivores      |



| animal_catg_id    | animal_catg_name      |   catg_id |
| —————-        |————————-                  |————————-  |
| 1             |   Dogs                    |   1       |
| 2             |   Cats                    |   1       |
| 3             |   Cows                    |   2       |
| 4             |   Buffalo                 |   2       |



| animal_id     | animal_name   | animal_catg_id    |
| —————-        |————————-      |   ————————-       |
| 1             |   labs        |   1               |
| 2             |   pugs        |   1               |
| 3             |   terriers    |   1               |
| 4             |   german      |   1               |
| 5             |   lion        |   2               |
| 6             |   tiger       |   2               |

I am assuming it would be hierarchical query, i have never written one before, i need some help with that. I don't know where to start and how to start it.

EDIT

One of the comments in the answers is that the schema design is not clear. What changes should I do to make to get the data in the json format, so that it maintains the hierarchy

EDIT2

My current query returns this table

Carnivores     |  Dogs    | labs
Carnivores     |  Dogs    | pugs
Carnivores     |  Dogs    | terriers
.......

Answer 1

Here's an example of a hierarchical query from the Oracle documentation:

SELECT last_name, employee_id, manager_id, LEVEL
  FROM employees
  START WITH employee_id = 100
  CONNECT BY PRIOR employee_id = manager_id
  ORDER SIBLINGS BY last_name;

http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

Something like this in your case but your schema design isn't clear

 SELECT animal_name, level
  FROM animals
  START WITH parentid is null
  CONNECT BY PRIOR id = parentid;

Answer 2

The JSON you are proposing appears to have no correlation between the IDs you are assigning and the IDs in the tables this will make it difficult to connect anything from the client-side back to the database.

You would be better re-organising your tables so that you can put everything into a single hierarchical structure. Something like a Linnaean Taxonomy:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Taxonomies ( ID, PARENT_ID, Category, Taxonomy, Common_Name ) AS
          SELECT  1, CAST(NULL AS NUMBER),  'Kingdom',     'Animalia',    'Animal'       FROM DUAL
UNION ALL SELECT  2,  1,    'Phylum',      'Chordata',    'Chordate'     FROM DUAL
UNION ALL SELECT  3,  2,    'Class',       'Mammalia',    'Mammal'       FROM DUAL
UNION ALL SELECT  4,  3,    'Order',       'Carnivora',   'Carnivore'    FROM DUAL
UNION ALL SELECT  5,  4,    'Family',      'Felidae',     'Feline'       FROM DUAL
UNION ALL SELECT  6,  5,    'Genus',       'Panthera',    'Tiger'         FROM DUAL
UNION ALL SELECT  7,  5,    'Genus',       'Felis',       'Cat'           FROM DUAL
UNION ALL SELECT  8,  5,    'Genus',       'Lynx',        'Lynx'          FROM DUAL
UNION ALL SELECT  9,  4,    'Family',      'Canidae',     'Canid'        FROM DUAL
UNION ALL SELECT 10,  9,    'Genus',       'Canis',       'Canine'       FROM DUAL
UNION ALL SELECT 11, 10,    'Species',     'Canis Lupus', 'Gray Wolf'     FROM DUAL
UNION ALL SELECT 12, 11,    'Sub-Species', 'Canis Lupus Familiaris', 'Domestic Dog' FROM DUAL
UNION ALL SELECT 13, 12,    'Breed',       NULL,          'Pug'           FROM DUAL
UNION ALL SELECT 14, 12,    'Breed',       NULL,          'German Shepherd' FROM DUAL
UNION ALL SELECT 15, 12,    'Breed',       NULL,          'Labradors'     FROM DUAL
UNION ALL SELECT 16,  7,    'Species',     'Felis Catus', 'Domestic Cat'  FROM DUAL
UNION ALL SELECT 17,  8,    'Species',     'Lynx Lynx',   'Eurasian Lynx' FROM DUAL
UNION ALL SELECT 18,  8,    'Species',     'Lynx Rufus',  'Bobcat'        FROM DUAL;

Then you can extract the data relatively simply:

Query 1 - Get everything taxonomically related to "Cat":

SELECT *
FROM (
  SELECT *
  FROM   Taxonomies
  START WITH Common_Name = 'Cat'
  CONNECT BY PRIOR PARENT_ID = ID
  ORDER BY LEVEL DESC
)
UNION
SELECT *
FROM (
  SELECT *
  FROM   Taxonomies
  START WITH Common_Name = 'Cat'
  CONNECT BY PRIOR ID = PARENT_ID
  ORDER SIBLINGS BY Common_Name
)

Results:

| ID | PARENT_ID | CATEGORY |    TAXONOMY |  COMMON_NAME |
|----|-----------|----------|-------------|--------------|
|  1 |    (null) |  Kingdom |    Animalia |       Animal |
|  2 |         1 |   Phylum |    Chordata |     Chordate |
|  3 |         2 |    Class |    Mammalia |       Mammal |
|  4 |         3 |    Order |   Carnivora |    Carnivore |
|  5 |         4 |   Family |     Felidae |       Feline |
|  7 |         5 |    Genus |       Felis |          Cat |
| 16 |         7 |  Species | Felis Catus | Domestic Cat |

Query 2 - Get everything taxonomically related to "Canine":

SELECT *
FROM (
  SELECT *
  FROM   Taxonomies
  START WITH Common_Name = 'Canine'
  CONNECT BY PRIOR PARENT_ID = ID
  ORDER BY LEVEL DESC
)
UNION
SELECT *
FROM (
  SELECT *
  FROM   Taxonomies
  START WITH Common_Name = 'Canine'
  CONNECT BY PRIOR ID = PARENT_ID
  ORDER SIBLINGS BY Common_Name
)

Results:

| ID | PARENT_ID |    CATEGORY |               TAXONOMY |     COMMON_NAME |
|----|-----------|-------------|------------------------|-----------------|
|  1 |    (null) |     Kingdom |               Animalia |          Animal |
|  2 |         1 |      Phylum |               Chordata |        Chordate |
|  3 |         2 |       Class |               Mammalia |          Mammal |
|  4 |         3 |       Order |              Carnivora |       Carnivore |
|  9 |         4 |      Family |                Canidae |           Canid |
| 10 |         9 |       Genus |                  Canis |          Canine |
| 11 |        10 |     Species |            Canis Lupus |       Gray Wolf |
| 12 |        11 | Sub-Species | Canis Lupus Familiaris |    Domestic Dog |
| 13 |        12 |       Breed |                 (null) |             Pug |
| 14 |        12 |       Breed |                 (null) | German Shepherd |
| 15 |        12 |       Breed |                 (null) |       Labradors |